Passing in a large string of Guids
I have a stored procedure with 2 issues.
It has 40 parameters. I know the first comment is going to be to re-design
my stored procedure so it doesn't have 40 parameters. However, this is a
search form with a big criteria section. So the user is specifying up to
40 different criteria for the search. Then we're passing in these values
each as a parameter. Right now I have a 40 parameter sproc. Would it be
more efficient to pass these in as an XML parameter and parse it inside or
a table parameter (we are still running SQL 2k5 but are considering an
upgrade to 2k12).
Three of my parameters are long strings of Guid values separated by quotes
and commas. Basically the user is presented with a list of product lines,
hundreds of them at times. They then click the ones they want to search.
We've limited the number of lines they can check just because the strings
get too long, but we're passing a long string of Guids separated by quotes
and commas. I know this isn't the right way to do this. What would be the
standard Trans SQl pattern for passing in arrays or collections of Guid
values like this? I have 3 separate fields of the 40 doing this. We want
to do this more efficiently and also be able to pass in more than our
current limitation.
No comments:
Post a Comment