setting a keyword at runtime

Question

setting a keyword at runtime

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

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'

ozy_osborne@hotmail.com Newbie Points: 5 More actions · Answer 3

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.

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 4

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.