CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-proceduresoptional-parameters
larryq
larryq

Reputation: 16309

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

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!

Upvotes: 4

Views: 5428

Answers (2)

UserControl
UserControl

Reputation: 15179

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

Upvotes: 2

gbn
gbn

Reputation: 432657

You can use this or an "OR" contsruct

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


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

Upvotes: 11

Related Questions