Issue with replace function

  • 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 ...

  • 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/61537
  • 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/

  • 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.

  • shall i send you the entire porocedure..

  • 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