Passing a list of system.guid as params to as SQL2008 sproc

Question

I would like to pull a set of records in a SQL sproc by passing in a list of system.guid and also by passing in a list of integer keys.

I can put the guids ihto a string, of course, but the question is - how do I quote the test values so they are recognized as UIDs by SQL

In essence I was to

DECLARE @pklist AS VARCHAR(1000)

-- This would look like "'45F4AE2A-D27C-D711-83FD-0008C7FA9683','EC824D02-D37C-D711-83FD-0008C7FA9683','BA8E4D02-D37C-D711-83FD-0008C7FA9683'" coming out of the execsql from vb .net - I think :-) but it seems it needs to look like

SET @pklist = '45F4AE2A-D27C-D711-83FD-0008C7FA9683,EC824D02-D37C-D711-83FD-0008C7FA9683,BA8E4D02-D37C-D711-83FD-0008C7FA9683'

SELECT * from dbo.members WHERE members.cpk IN (@pklist)

This only returns the record matching the first UID in the string. Is there something I can wrap a t-sql VARCHAR string with so each guid will be seen distinctly?

In trying to do this with a list of integer keys, the problem is the value is seen as part of a string and is not cast to an integer

It would seem creating a table from a parsed version of the string passed in and then doing a JOIN would accomplish what I need as well, but struggling with the T-SQL syntax for that.

So basically - pass in a list( of T) into a sproc and get a record set

All guidance appreciated.

Charles Hankey

( also guidance on why neither of my perfectly valid email addresses are acceptable on the notify - have it set up in profile )

Answer 1

SQL Server 2008 introduced Table-Valued Parameters for just this sort of scenario. Have a read through that article.