unknown number of parameters for sp
-
Hell all,
I have a situation where a stored procedure is to delete all records from a table except those with an ID supplied by a user. The challange is that I would like to allow the user to supply a variable number of ID's. The logic is like this:
DELETE tblName
WHERE
tblName.ID <> p1 AND
tblName.ID <> p2 AND
so on ...
Suggestions on how to allow variable number of p's appreciated.
Thank you!
JM
-
There have been a couple of threads recently where a set of values is passed in via a single parameter. The query then does a charindex() in order to "parse" those separate values. That should work and you won't have to worry about extraneous parameters you won't necessarily use.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Insert the exceptions into a temporary table and then delete the rows that don't have IDs in the temporary table.
--Jonathan
--Jonathan -
Hey, Jonathan, that's a new way!
Not the typical 'how to pass an array' stuff
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you all,
Just had time to get back to this. I also found the following:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
JM
-
I would go for the temporary table, sounds like the code is going to be more straightforward with your NOT IN or whatever.
Regards, Hanslindgren
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply