Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

|||in t-sql you can have conditional where clauses
sample:

select * from mytable
where
mycol = case when @.i = 1 then 1 else 2 end