Link to home
Start Free TrialLog in
Avatar of Ike23
Ike23Flag for United States of America

asked on

Set variable in SQL Server query populated with list of strings

I have a saved .sql script that I run a few different queries on using a list an IN() statement and a list of strings like below

--SET A VARIABLE FOR THE LIST OF STRINGS
DECLARE @QLIST varchar(2000) = 'STRING1','STRING2','STRING3','STRING4'


SELECT     ID
FROM      myTable1  
WHERE     qString IN (@QLIST)
ORDER BY PID

The problem I'm having is setting the actual @QLIST variable to be a comma delemited list with single quotes still surrounding each list item.

I searched around and didn't find what I'm looking for and thought one of the SQL experts here would have tried this before.

Thanks in advance!
Avatar of TempDBA
TempDBA
Flag of India image

So, what is your requirement. I mean how you want to pass your parameter?
SOLUTION
Avatar of Daniel Reynolds
Daniel Reynolds
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ike23

ASKER

The query already works, I just have multiple queries using the same list.  So an example of the query as it works today is:

SELECT     ID
FROM      myTable1  
WHERE     qString IN ('STRING1','STRING2','STRING3','STRING4')
ORDER BY PID

I just thought it would be easier to set the variable once at the top and then not have to copy and paste the list every time I want to use a different list.

In web coding there is a way to set a single quoted list and pass that as a variable so I was hoping that there is a quick easy way to set this in SQL.  If not, no big deal.

Thanks.
Maybe something like this?


SELECT     ID 
FROM      myTable1   
WHERE     qString IN ( + @QLIST + ',')
ORDER BY PID

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It should work as dynamic sql
--SET A VARIABLE FOR THE LIST OF STRINGS
DECLARE @QLIST varchar(2000) = '''STRING1'',''STRING2'',''STRING3'',''STRING4'''



declare @sql nvarchar(2000)
set @sql ='select ID
FROM      myTable1  
WHERE     qString in ('+@QLIST+ ')
ORDER BY PID'
EXEC sp_executesql @sql
Avatar of Ike23

ASKER

I tried the dynamic sql and it gives an error.  It basically does exactly the same thing as just running the query without the EXEC.   I did a PRINT @sql and it shows that the list is comma delimeted but it doesn't have the single quotes around each item in the list.

That is what I need.  A comma delimited list with keeping the single quotes since these are not integers.  I'm surprised there isn't a function for this.  Maybe I'll just write it in a ColdFusion page which would take no time at all.

I'm determined to figure this out in my SQL Server Management Studio though.  I might try the Replace() function and try to replace the comma with a single quote and comma.