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