Passing an array of unique identifiers to a stored procedure

Question

Possible Duplicate:
Parameterizing an SQL IN clause?
Passing an array of parameters to a stored procedure

I need to pass a collection of unique identifiers to my stored procedure. I wrote this query to test what I want to do:

SELECT  *
FROM    MyTable
WHERE   MyTable.TypeId IN ('852D7F39-302A-4E35-94D4-9A0335F8E9DC,1DF6EAA0-D0EC-4E4D-9E6B-1779F0E42A82')

This query runs correctly. So I created this stored procedure:

CREATE PROCEDURE [dbo].[MySproc] (
@TypeIdsList varchar(8000)
) AS

BEGIN
EXEC(
    'SELECT *
    FROM    MyTable
    WHERE   MyTable.TypeId IN (' + @TypeIdsList + ')'
)
END

But when I execute it with some sample data:

DECLARE @return_value int

EXEC    @return_value = [dbo].[MySproc]
        @TypeIdsList = N'852D7F39-302A-4E35-94D4-9A0335F8E9DC,1DF6EAA0-D0EC-4E4D-9E6B-1779F0E42A82'

SELECT  'Return Value' = @return_value

GO

I get this error:

Incorrect syntax near 'D7F39'.

Anyone know what I'm doing wrong?

score 2 · Accepted Answer · answered Jan 14 '13 at 20:00

<ids>
    <id value='852D7F39-302A-4E35-94D4-9A0335F8E9DC'/>
    <id value='1DF6EAA0-D0EC-4E4D-9E6B-1779F0E42A82'/>
</ids>


create procedure [dbo].[MySproc] (@TypeIdsList xml)
as 
begin

    declare @idoc int
    exec sp_xml_preparedocument @idoc output, @TypeIdsList
    create table #ids (id uniqueidentifier)

    insert into #ids
            select a.value
                from openxml (@idoc, '/ids/id',1)
            with (value uniqueidentifier) a

    select t.*
        from MyTable t 
            inner join #ids i with (nolock) on t.TypeId = i.id     

-- or
-- SELECT t.*
-- FROM MyTable t
-- WHERE t.TypeId IN (SELECT i.id FROM #ids WITH(NOLOCK))
end

score 0 · Answer 2 · answered Jan 14 '13 at 18:52

I'm confused. What do you mean that your first query runs correctly? To a human (at least this one), it would seem that you are looking for two values: '852D7F39-302A-4E35-94D4-9A0335F8E9DC' or '1DF6EAA0-D0EC-4E4D-9E6B-1779F0E42A82'. However, SQL interprets your expression as one value, '852D7F39-302A-4E35-94D4-9A0335F8E9DC,1DF6EAA0-D0EC-4E4D-9E6B-1779F0E42A82', with a comma in it.

Your stored procedure seems to want to turn this into two values. However, you have a problem with the commas. You need something like:

WHERE   MyTable.TypeId IN (''' + replace(@TypeIdsList, ',', '''') + ''')'

Oh, that dynamic SQL can get confusing. It might be easier just to use charindex() or like in regular SQL:

where charindex(','+MyTable.TypeId+',' , ','+@TypeIdsList+',') > 0

or

where ','+@TypeIdsList+',' like '%,'+MyTable.TypeId+',%'

Passing an array of unique identifiers to a stored procedure

2 Answers2