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

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!

gbn · Accepted Answer · 2009-11-12 16:13:06Z

11

You can use this or an "OR" contsruct

... WHERE GroupID = ISNULL(@GroupdID, GroupID) ... WHERE GroupID = @GroupdID OR @GroupdID IS NULL

answered Nov 12, 2009 at 16:13

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

larryq Over a year ago

Thank you. This is working, but I'm a little confused on the syntax. If I pass in a @GroupID value does SQL ignore the "OR @GroupID IS NULL" bit?

gbn Over a year ago

yes. If you pass in "42", "42 IS NULL" evaluates to false. Only the "GroupID = 42" condition decides what happens

UserControl · Accepted Answer · 2009-11-12 16:20:14Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related