How to replace single quotes in SP [Resolved]

Posted by Parcha under Sql Server on 9/12/2011 | Points: 10 | Views : 2586 | Status : [Member] | Replies : 3
Hi
this is my query
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @BusinessName + '%' ...

When i execute my SP output Shouldbe like this

AccountNo BusinessName
10509 Anderson's "The Grocery Store".......

But when iexecut emy SP
--EXEC DBSP_SF_GetAccountsLikeAccNo '10509','Anderson's "The Grocery Store"'
Getting error like...........
Incorrect syntax near 's'.
Unclosed quotation mark after the character string ''.

So when i execute my SP adding another Apostrophe after anderson
like EXEC DBSP_SF_GetAccountsLikeAccNo '10509','Anderson''s "The Grocery Store"'
ite executed so i want to replace how to replace using replace function in SP
plz help me




Responses

Posted by: T.saravanan on: 9/12/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Try this....

ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo] 

@AccountNo VARCHAR(20),
@BusinessName VARCHAR(40),
@Type VARCHAR(2)
AS
BEGIN
SELECT @BusinessName = REPLACE(@BusinessName,'''','''''')
IF @Type = 'AN'
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like @AccountNo + '%'
END
ELSE IF @Type = 'BN'
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @BusinessName + '%'
END
END


Thanks,
T.Saravanan

Parcha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Parcha on: 9/12/2011 [Member] Starter | Points: 25

Up
0
Down
This is my sp
ALTER PROCEDURE [dbo].[DBSP_SF_GetAccountsLikeAccNo]
@AccountNo VARCHAR(20),
@BusinessName VARCHAR(40),
@Type VARCHAR(2)
AS
BEGIN
IF @Type = 'AN'
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE AccountNo like @AccountNo + '%'
END
ELSE IF @Type = 'BN'
BEGIN
SELECT AccountNo, BusinessName FROM CustomerDetails WITH (NOLOCK) WHERE BusinessName like @BusinessName + '%'

END
END

Parcha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Parcha on: 9/12/2011 [Member] Starter | Points: 25

Up
0
Down
@T.saravanan
Thank u its working

Parcha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response