setting a keyword at runtime

  • 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

  • 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

  • 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'

  • 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.

  • 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