setting up variables to be used for an IN clause

  • Matthew Cushing

    SSCrazy

    Points: 2898

    I am trying to write some scripts so that other users can update things as needed. I'm trying to make them as safe as possible, so I want to try and create variables that they change and don't touch the other code.

    I have an in clause for this query that I'd like to be able to pass in multiple strings:

    declare @xvarchar(MAX)

    set @x = ('ABC-123','ABC-345','BGH-09876')

    select * from table

    where numberColumn in

    (@x)

    I don't want to go over the top and create a huge script, just looking for a way to pass multiple strings to an IN clause later in the script.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    The only way you can do that is by using dynamic SQL or a string splitter function.

    WHERE SomeCol IN (@Var) means WHERE SomeCol = @Var

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Matthew Cushing

    SSCrazy

    Points: 2898

    Yeah, I thought it might get more complicated, thanks for confirming. I think I might have a way of doing it, I'll post later if I get it to work the way I want it to.:-D

  • Lowell

    SSC Guru

    Points: 323444

    the function found at the end of this article is very highly rated among my peers here on SSC:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    select * from table

    where numberColumn in

    (SELECT Item FROM dbo.DelimitedSplit8K(@x,',')

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply