setting up variables to be used for an IN clause

Question

Post reply

setting up variables to be used for an IN clause

Matthew Cushing

SSCrazy

Points: 2898
More actions
December 11, 2013 at 12:57 pm

#281023

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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: 323518 More actions · Answer 3

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!