Ike23
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','STRIN G3','STRIN G4'
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!
--SET A VARIABLE FOR THE LIST OF STRINGS
DECLARE @QLIST varchar(2000) = 'STRING1','STRING2','STRIN
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!
So, what is your requirement. I mean how you want to pass your parameter?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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','STRI NG3','STRI NG4')
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.
SELECT ID
FROM myTable1
WHERE qString IN ('STRING1','STRING2','STRI
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
--SET A VARIABLE FOR THE LIST OF STRINGS
DECLARE @QLIST varchar(2000) = '''STRING1'',''STRING2'','
declare @sql nvarchar(2000)
set @sql ='select ID
FROM myTable1
WHERE qString in ('+@QLIST+ ')
ORDER BY PID'
EXEC sp_executesql @sql
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.
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.