December 23, 2003 at 12:25 pm
Good afternoon,
I have a stored procedure that is passed a comma seperated list of ID numbers as a parameter. I parse these ID's into a temp table (#tblTemp).
I then have:
DELETE FROM tblOne
WHERE tblOne.ID IN
(SELECT * FROM #tblTemp)
I would like to create a second parameter so that the user who executes the sp can set whether records are deleted based on inclusion in the list or exclusion.
Example:
EXEC spMyProcedure ('not in', '502,503,504')
EXEC spMyProcedure ('in', '502,503,504')
How can I set keywords "IN" vs. "NOT IN" based upon an input parameter?
Thank you
JM
December 23, 2003 at 12:46 pm
I'm not sure you could do it that way, but do you have any reason why it can't be two different stored procedures? For example:
EXEC spMyProcedureIN ('502,503,504')
EXEC spMyProcedureNOTIN ('502,503,504')
-SQLBill
December 23, 2003 at 12:53 pm
Try dynamical query.
create proc spMyProcedure @op varchar(10), @ids varchar(20)
as
declare @cmd varchar(255)
select @cmd = 'DELETE FROM tblOne WHERE tblOne.ID ' + @op + ' (SELECT * FROM #tblTemp)'
exec (@cmd)
EXEC spMyProcedure @op = 'not in', @ids = '502,503,504'
EXEC spMyProcedure @op = 'in', @ids = '502,503,504'
December 24, 2003 at 2:35 am
assuming that you can change the @op parameter to a bit field then this will work:
delete tblOne
from tblOne T1
left join #tblTemp t on t.tblTempId = T1.tblOneid
where isnull(t.tblTempId / t.tblTempId,0) = @op
Plus there is no sign of dynamic sql!
send a 1 in the @op parameter to delete intersecting records, a 0 to delete records not in the temp table.
December 29, 2003 at 12:04 pm
Thanks all for your suggestions!
I did use dynamic sql to get this done. This sp will be used infrequently by only a small inhouse group of techies so I think dynamic sql is justified.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy