November 23, 2011 at 9:48 am
here i have a requiremnet to select multiple option for @selection user parameter.
declare @selection varchar(20)
declare @bank varchar(20)
Select @Selection = REPLACE(@Selection,',',''',''')
SET @bank=''
IF 'abc' in (@Selection) --If abc is in @selection then we are taking bank
BEGIN
Select @bank = '''' + bank + '''' --"abc","def"--bank is taking abc and def as separate string with double cotes separated by comma.we can expect output as abc,def
from firm
where oldfirmcode = 'abc'
END
IF 'def' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + '''' --In the second step taking bankFusiCode and adding to above variables
from firm
where oldfirmcode = 'def'
END
IF 'ghi' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + ''''--In the bank group for these values if we have data,then we get the output.
from firm
where oldfirmcode = 'ghi'
END
when i am trying to execute this query i am getting error at replace function and
i) in correct syntax near replace function
2) A select statemnet that assigns a value to a variable must not be combined with data retrieval operations
can anyone correct my query ...
November 23, 2011 at 9:54 am
This sort of construct won't work
IF 'abc' in (@Selection) -- If abc is in @selection then we are taking bank
You could try
IF @Selection LIKE '%,abc,%'
instead
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537November 23, 2011 at 9:55 am
You need to add apostrophes to your dynamic SQL. Instead of the line:
Select @bank = @bank + '','' + bank + '''' -- In the second step taking bankFusiCode and adding to above variables
You should use this line:
Select @bank = @bank + ''',''' + bank + '''' -- In the second step taking bankFusiCode and adding to above variables
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2011 at 12:06 pm
atlancharlo (11/23/2011)
here i have a requiremnet to select multiple option for @selection user parameter.declare @selection varchar(20)
declare @bank varchar(20)
Select @Selection = REPLACE(@Selection,',',''',''')
SET @bank=''
IF 'abc' in (@Selection) --If abc is in @selection then we are taking bank
BEGIN
Select @bank = '''' + bank + '''' --"abc","def"--bank is taking abc and def as separate string with double cotes separated by comma.we can expect output as abc,def
from firm
where oldfirmcode = 'abc'
END
IF 'def' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + '''' --In the second step taking bankFusiCode and adding to above variables
from firm
where oldfirmcode = 'def'
END
IF 'ghi' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + ''''--In the bank group for these values if we have data,then we get the output.
from firm
where oldfirmcode = 'ghi'
END
when i am trying to execute this query i am getting error at replace function and
i) in correct syntax near replace function
2) A select statemnet that assigns a value to a variable must not be combined with data retrieval operations
can anyone correct my query ...
Is all of the above part of a stored procedure? If so, how about providing the entire stored procedure along with how it is being called. We may be able to provide you with a better solution.
November 23, 2011 at 12:23 pm
shall i send you the entire porocedure..
November 23, 2011 at 12:25 pm
you are saying just to replace that step instead of mine....beyond that my entire code is correct....can you correct at the replace function please its getting error incorrect syntax near replace..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply