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

[bencoding]

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

[ck9663]

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:

WHERE report_field_value = case when @report_field_value = 0 then report_field_value else  @report_field_value end
 

-- CK

[bencoding]

Try this:

WHERE report_field_value = case when @report_field_value = 0 then report_field_value else  @report_field_value end
 

-- 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.

[ck9663]

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

[bencoding]

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