>Struggling with t-SQL syntax to get a set of record by passing in a string built in vb .net from a list of primary keys. Need 2 versions - one for integer keys, one for guid / uid keys
>- I can be flexible about what and how I pass the params
>I understand t-sql does not actually have arrays so my first instinct is to pass in a string with the guids delimited with ' and separated with a comma and see if the pk is in the "list" of the string.
>
>But that won't work with
>
>select * from members where cpk in (@pklist)
>
>if I leave out the delimeters and have a single string with single quotes surrounding it the above select works but only pulls the first record
>
>Integers an even bigger disaster as they a string values and the above code doesn't cast them
>
>Plan B would be to build a table from the incoming param string and join against that to get the recordset but the syntax for that is beyond my t-sql skills
>
>Have a feeling somebody has the answer to this
>
>Guidance appreciated
>
>TIA

Hi Charles,

Believe it or not, but this is the MOST common question on ASP.NET SQL Server forum. There are several ways of solving it and in SQL Server 2008 you can actually pass a data table to the SP.

Anyway, if you want to be traditional and pass comma-delimited string, then take a look at

http://forum.lessthandot.com/viewtopic.php?f=17&t=7566

and an excellent blog http://www.sommarskog.se/arrays-in-sql-2005.html by Erland Sommarskog comparing different methods of splitting a string.

Once you split the string into the table, then

select myTable.* inner Join dbo.ufn_split(@MyCommaDelimitedList, ',') F on MyTable.PkField = F.ValueField

IMHO, you can have two versions of split function - one for character fields and another for Integer even though in that discussion referenced above someone advocated against it.