Using a IF block INSIDE the where clause of a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a IF block INSIDE the where clause of a SP

Author

Topic

David Borneman
Starting Member

2 Posts

Posted - 2005-05-19 : 17:44:10

Hiya all!

Going to try to keep this simple, and not give any information that is not required.

I need to be able to execute selective criteria in a where clause. For instance, I have tried the following with no success:

Select Name, DOB from Members WHERE Member.Active = 1

IF @CheckNewMember = 1
BEGIN
AND Member.New = 1
END

That is a very simplified version... the goal is to add the criteria "Member.New = 1" If, and ONLY if the passed variable @CheckNewMember = 1.

The actual code I will be using containes 30 or so conditional clauses... but I need to know if using a IF/BEGIN/END/ELSE block inside a where clause is even possable - I cannot seem to get it to work. If not a IF block, then can someone give me a kick in the right direction?

Thanks much!

Dave Borneman

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 17:49:19

You can use CASE in the WHERE clause:

WHERE Member.Active = CASE WHEN @CheckNewMember = 1 THEN 1 ELSE Member.Active END

So if @CheckNewMember = 1, then WHERE Member.Active = 1
If @CheckNewMember != 1, then WHERE Member.Active = Member.Active (kinda like 1=1)

Tara

David Borneman
Starting Member

2 Posts

Posted - 2005-05-19 : 17:57:25

Nice! - You earned your title! (Almighty SQL Goddess)... Thanks much!!

Dave

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 18:03:49

No CASE needed. All you need is a little correct boolean logic in your WHERE clause:

WHERE @CheckNewMember <> 1 or (Member.New = 1)

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-19 : 18:07:02

quote:

WHERE @CheckNewMember <> 1 or (Member.New = 1)

For some reason, I find it hard to read. My brain just doesn't work well with ORs.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 18:18:35

you can negate it if that's easier to read:

WHERE NOT (@CheckNewMember=1 and MemberNew <> 1)

to me, either of those is much more clear than

WHERE Member.Active = CASE WHEN @CheckNewMember = 1 THEN 1 ELSE Member.Active END

and of course much more flexible and portable.

for more on this, see: http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources