Optional where clause / parameter in a SQL 2008 stored proc?

Question

Hi everyone,

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

Answer 3

This article might be useful: http://www.sommarskog.se/dyn-search-2005.html