Inserting a string of form "GUID1, GUID2, GUID3 ..." into an IN statement in TSQL

Question

Hi,

I've got a stored procedure in my database, that looks like this

ALTER PROCEDURE [dbo].[GetCountingAnalysisResults] 
    @RespondentFilters varchar
AS
BEGIN

@RespondentFilters = '''8ec94bed-fed6-4627-8d45-21619331d82a, 114c61f2-8935-4755-b4e9-4a598a51cc7f'''

DECLARE @SQL nvarchar(600)

SET @SQL = 
   'SELECT *
    FROM Answer
    WHERE Answer.RespondentId IN ('+@RespondentFilters+'''))
    GROUP BY ChosenOptionId'

exec sp_executesql @SQL

END

It compiles and executes, but somehow it doesn't give me good results, just like the IN statement wasn't working. Please, if anybody know the solution to this problem, help me.

Answer 1

It looks like you don't have closing quotes around your @RespondentFilters

'8ec94bed-fed6-4627-8d45-21619331d82a, 114c61f2-8935-4755-b4e9-4a598a51cc7f'

Since GUIDs do a string compare, that's not going to work.

Your best bet is to use some code to split the list out into multiple values.

Something like this:

-- This would be the input parameter of the stored procedure, if you want to do it that way, or a UDF
declare @string varchar(500)
set @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'


declare @pos int
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@string),1)  ','
 set @string = @string  + ','

set @pos =  patindex('%,%' , @string)
while @pos  0
begin
 set @piece = left(@string, @pos - 1)

 -- You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%,%' , @string)
end

Code stolen from Raymond Lewallen

Answer 2

I think you need quotes inside the string too. Try:

@RespondentFilters = '''8ec94bed-fed6-4627-8d45-21619331d82a'',''114c61f2-8935-4755-b4e9-4a598a51cc7f'''

You could also consider parsing the @RespondentFilters into a temporary table.

Answer 3

You need single quotes around each GUID in the list

@RespondentFilters = '''8ec94bed-fed6-4627-8d45-21619331d82a'', ''114c61f2-8935-4755-b4e9-4a598a51cc7f'''

Answer 4

You should definitely look at splitting the list of GUIDs into a table and joining against that table. You should be able to find plenty of examples online for a table-valued function that splits an input string into a table.

Otherwise, your stored procedure is vulnerable to SQL injection. Consider the following value for @RespondentFilters:

@RespondentFilters = '''''); SELECT * FROM User; /*'

Your query would be more secure parsing (i.e. validating) the parameter values and joining:

SELECT *
FROM Answer
WHERE Answer.RespondentId IN (SELECT [Item] FROM dbo.ParseList(@RespondentFilters))
GROUP BY ChosenOptionId

or

SELECT *
FROM Answer
INNER JOIN dbo.ParseList(@RespondentFilters) Filter ON Filter.Item = Answer.RespondentId
GROUP BY ChosenOptionId

It's slightly more efficient as well, since you aren't dealing with dynamic SQL (sp_executesql will cache query plans, but I'm not sure if it will accurately identify your query as a parameterized query since it has a variable list of items in the IN clause).

Answer 5

Tank you all for your ansewers. They all helped a lot. I've dealt with the problem by writing a split function, and it works fine. It's a litte bit overhead from what I could have done, but you know, the deadline is hiding around the corner :)