Question

I m writing some code that updates a table. Depending on what the user wants to do, it either updates a large set of records, or a smaller one. The delineating factor is a group ID.

The user can choose whether to update the table for all records, or just those with that groupID. I d like to use the same stored procedure for both instances, with maybe a little logic in there to differentiate between the scenarios. (I d prefer not to write two stored procs with 90% identical code.)

I m no expert at stored procedures and am not sure if I can pass in optional parameters, or how to dynamically generate part of a where clause, depending on whether the groupID is there or not. Any suggestions are welcome.

Thanks!

Answer 1

You can use this or an "OR" contsruct

... WHERE GroupID = ISNULL(@GroupdID, GroupID)


... WHERE GroupID = @GroupdID OR @GroupdID IS NULL

Answer 2

create procedure MyProc (@GroupID int = null)
as
begin
    update MyTable set ....
    where @GroupID is null or GroupID = @GroupID
end

友情链接