By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,359 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,359 IT Pros & Developers. It's quick & easy.

Newbie to T-SQL (and can't get my if..else to work in WHERE clause)

bencoding
P: 22
Hello, I'm new to this board and new to T-SQL,

I have a project at my work that is requiring me to modify an existing stored procedure to add more functionality.

How can I use a CASE or IF...ELSE (preferably a single IF) statement for this scenario?

if @report_field_value is equal to 0 then leave that where clause out completely else if it is a number greater than 0 then use the where clause of WHERE report_field_value = @report_field_value

Thx
Ben
Mar 20 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Hello, I'm new to this board and new to T-SQL,

I have a project at my work that is requiring me to modify an existing stored procedure to add more functionality.

How can I use a CASE or IF...ELSE (preferably a single IF) statement for this scenario?

if @report_field_value is equal to 0 then leave that where clause out completely else if it is a number greater than 0 then use the where clause of WHERE report_field_value = @report_field_value

Thx
Ben
Try this:


Expand|Select|Wrap|Line Numbers
  1. WHERE report_field_value = case when @report_field_value = 0 then report_field_value else  @report_field_value end
  2.  
-- CK
Mar 20 '08 #2

bencoding
P: 22
Try this:


Expand|Select|Wrap|Line Numbers
  1. WHERE report_field_value = case when @report_field_value = 0 then report_field_value else  @report_field_value end
  2.  
-- CK

yes, I have seen where you can add the CASE statement right after the "=, equal" operator but what I really need is to leave the WHERE clause out all together if @report_field_value = 0.

But actually I think what you provided will still work I didn't think about that, thanks.
Mar 20 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Option 1: Since the where condition is always true for all records, it's as if there's not where at all.

Option 2: Create a dynamic sql.

-- CK
Mar 20 '08 #4

bencoding
P: 22
Option 1: Since the where condition is always true for all records, it's as if there's not where at all.

Option 2: Create a dynamic sql.

-- CK

Thanks for the alternative option, I'm not a big fan of dynamic sql though, I have heard it can slow down some larger sp's. Not to mention not as easy to follow or debug. Option 1 seems to work perfectly for me. Thanks, Ben
Mar 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.