sql - Passing in a large string of Guids -

i have stored procedure 2 issues.

1. it has 40 parameters. know first comment going to re-design stored procedure doesn't have 40 parameters. however, search form big criteria section. user specifying 40 different criteria search. we're passing in these values each parameter. right have 40 parameter sproc. more efficient pass these in xml parameter , parse inside or table parameter (we still running sql 2k5 considering upgrade 2k12).

2. three of parameters long strings of guid values separated quotes , commas. user presented list of product lines, hundreds of them @ times. click ones want search. we've limited number of lines can check because strings long, we're passing long string of guids separated quotes , commas. know isn't right way this. standard trans sql pattern passing in arrays or collections of guid values this? have 3 separate fields of 40 doing this. want more efficiently , able pass in more our current limitation.

sql server 2005

until can take advantage of table-valued parameters, suggest creating specialized table-valued udf splits guid parameters. use output in join, exists clause, cross apply, etc.

create function dbo.splitguids (    @list       varchar(max),    @delimiter  varchar(255) ) returns table schemabinding    return     (         select [guid] = convert(uniqueidentifier, x)       (          select x = rtrim(y.i.value('.[1]', 'nvarchar(4000)'))                  (            select x = convert(xml, '<i>'              + replace(@list, @delimiter, '</i><i>')              + '</i>').query('.')         ) cross apply x.nodes('i') y(i)       ) x len(item) > 0    ); go 

usage:

declare @guids varchar(max);  set @guids = 'e2072e08-84d3-4eea-a6ed-f38f1e4e34a6,'            + 'a6b047ba-647e-4b35-8d95-f4a204b860f6';  select [guid] dbo.splitguids(@guids, ',') g; 

results:

item ------------------------------------ e2072e08-84d3-4eea-a6ed-f38f1e4e34a6 a6b047ba-647e-4b35-8d95-f4a204b860f6 

the stored procedure might this:

create procedure dbo.whatever   @guids varchar(max)  begin   set nocount on;    select t.columns     dbo.sometable t     inner join dbo.splitguids(@guids, ',') g     on t.key = g.[guid]; end go 

(the function fail, of course, if of elements in string contain invalid guid. in sql server 2012 use try_convert() wouldn't need because you'd using tvps, more below.)

sql server 2008+

later when graduate sql server 2005 (and other readers face problem on sql server 2008+), can more efficiently using table type:

create type dbo.guids table(guid uniqueidentifier primary key); 

then stored procedure can take type input instead of big string:

create procedure dbo.whatever   @guids dbo.guids readonly begin   set nocount on;    select t.columns     dbo.sometable t     inner join @guids g     on t.key = g.[guid]; end go 

(note how easy switch tvps - lines 2 , 9 needed change.)

then web app can pass collection such datatable @guids parameter. no messy string splitting, no type conversions, no artificial limits on how many different guids can pass.