Case in where clause - SQL Server Forums

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)

Case in where clause

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-18 : 11:10:45

I have a SP that has a case in the where clause. In my case, I'm trying get that if the field is null then pull all that is null else pull all that is not null, and if they select 'ALL' from drop down then retreive all. I can't get it to work because it does not let me use "NULL". Can someone help?


SELECT 	C.ID, C.NAME, C.CITY, C.STATE, C.USER_3, OE.AMOUNT_PAID
FROM 	CUSTOMER C INNER JOIN ORDER_ENTRY OE
	ON C.ID = OE.CUST_ID
WHERE 	OE.DATE BETWEEN @STARTDATE AND @ENDDATE
	AND C.USER_3 LIKE CASE @STATUS
		WHEN 'ACTIVE' THEN NULL
		WHEN 'OTHERS' THEN NOT NULL
		WHEN 'ALL' THEN C.USER_3
		END
ORDER BY C.NAME

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-18 : 11:31:43

Try IS NULL maybe? <-- shot in the dark

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-18 : 11:33:19

Hi. Thanks for the reply. Using "IS NULL" will give me an error "Incorrect syntax near the keyword 'IS'."

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-18 : 11:34:59

The format of the case statement is wrong. Also, CASE cannot return a value of NOT NULL. You should look up CASE in SQL Server Book Online.

CASE C.USER_3 LIKE CASE @STATUS  -- Missing WHEN, Missing THEN
WHEN 'ACTIVE' THEN NULL
WHEN 'OTHERS' THEN NOT NULL  -- Cannot retrun NOT NULL
WHEN 'ALL' THEN C.USER_3
END

CODO ERGO SUM

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-18 : 11:53:01

This case should work. I use this with other SP that i have and works fine. It's just doesn't work with what I'm trying to pull, and that is base on what Parameter it will pull only records that contain that specific data within that roll.

If case can't pull NULL value, is there any other function that I can use in place of the case? Maybe use COALESCE?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 15:35:36

I hope i got your intention right:

SELECT 	C.ID, C.NAME, C.CITY, C.STATE, C.USER_3, OE.AMOUNT_PAID
FROM 	CUSTOMER C INNER JOIN ORDER_ENTRY OE
	ON C.ID = OE.CUST_ID
WHERE 	OE.DATE BETWEEN @STARTDATE AND @ENDDATE
  and ((@status is null and c.user_3 is null)
       or @Status = 'ALL'
       or (@Status is not null and c.User_3 is not null))

However, from performance point of view I would rather create 3 procs, test case condition on client and then call correct one from there.

jhermiz

3564 Posts

Posted - 2005-07-18 : 16:20:07

quote:
Originally posted by mmarovic

I hope i got your intention right:
SELECT C.ID, C.NAME, C.CITY, C.STATE, C.USER_3, OE.AMOUNT_PAID
FROM CUSTOMER C INNER JOIN ORDER_ENTRY OE
ON C.ID = OE.CUST_ID
WHERE OE.DATE BETWEEN @STARTDATE AND @ENDDATE
and ((@status is null and c.user_3 is null)
or @Status = 'ALL'
or (@Status is not null and c.User_3 is not null))
However, from performance point of view I would rather create 3 procs, test case condition on client and then call correct one from there.

Three stored procedures that all do the same thing? Why?
No the correct way is to use bool logic here. The problem is with ( ) with and's and or's.

Let me guess, this is a query for a report ?

If so try this:

Create a parameter called Status in the actual report and assign it the values 1, 2, 3 for ACTIVE, OTHERS, and ALL.

Then in your query you dont need a case at all it becomes:

SELECT C.ID, C.NAME, C.CITY, C.STATE, C.USER_3, OE.AMOUNT_PAID
FROM CUSTOMER C INNER JOIN ORDER_ENTRY OE
ON C.ID = OE.CUST_ID
WHERE (OE.DATE BETWEEN @STARTDATE AND @ENDDATE)
AND ((@Status=1 AND MyTable.STATUS IS NULL)
AND (@Status=2 AND MyTable.STATUS IS NOT NULL)
AND (@Status=3 AND (MyTable.STATUS IS NULL OR MyTable.STATUS IS NOT NULL)))

Pass in @Status as integer.

Good luck,

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 16:28:25

quote:
Three stored procedures that all do the same thing? Why?

They would not do the same thing. The performance would be gained if for one or two procedure index on customer.user_3 may be the best choice. However I agree that could be far fetched.

quote:
No the correct way is to use bool logic here. The problem is with ( ) with and's and or's.

That may be true, but your query certainly returns no rows.

quote:
SELECT C.ID, C.NAME, C.CITY, C.STATE, C.USER_3, OE.AMOUNT_PAID
FROM CUSTOMER C INNER JOIN ORDER_ENTRY OE
ON C.ID = OE.CUST_ID
WHERE (OE.DATE BETWEEN @STARTDATE AND @ENDDATE)
AND ((@Status=1 AND MyTable.STATUS IS NULL)
AND (@Status=2 AND MyTable.STATUS IS NOT NULL)
AND (@Status=3 AND (MyTable.STATUS IS NULL OR MyTable.STATUS IS NOT NULL)))

jhermiz

3564 Posts

Posted - 2005-07-18 : 16:30:05

Really did you try it ?

So you'd create 3 seperate queries just to handle a different parameter? Not good :)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 16:31:51

Many times. Good more often then not, from performance point of view.

jhermiz

3564 Posts

Posted - 2005-07-18 : 16:34:33

Sample data, and ddl mine works fine...

You can also try this:

AND (@Status <> 1 OR MyTable.Status IS NULL)
...
etc etc

I hope you are changing MyTable to your table names.

Could you imagine 3 different sprocs for something like this and creating the call to the right one on the client side? That is bad programming.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-18 : 16:39:46

Company i worked for had 8000 page hits per second average, so believe me it is good programming in such circumstances. If you write too generic queries you will not allways have the most efficient execution plan or you will have frequent sp recompilations and that can kill cpu.

Variant with or will actually work and it is equivalent to my code. The statetment you posted first dosn't return any row, it can't be true @status = 1 and @status = 2

Btw, query you quoted is correct.

jhermiz

3564 Posts

Posted - 2005-07-18 : 17:41:51

Did the company you work for execute that same query ? The execution plan on such a query such as this one to create 3 different stored procedures I would never take that route. You state an index on a column, and my response is if your client code is checking 3 text boxes for parameters just to figure out which condition to take to call the stored procedure outweighs one single stored procedure using simple and / or.

8000 page hits per second, you work for google ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-18 : 18:18:48

Thanks for all the great solutions.

mmarovic, your example does work and since we don't have that much records to retreive (about 200 records on the max), performance is no issue.

Jon, for your example MyTable.Status....shouldn't that be the C.USER_3 field? Just not sure how it knows that field C.USER_3 is suppose to be NULL or not. I have not try your example yet, but yes it is for use with reporting services. I will try it out tomorrow and let you know.

Thanks again all.

mmarovic
Aged Yak Warrior

518 Posts

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

quote:
Did the company you work for execute that same query?

No, but it is our coding standard to move such logic on middle tier or client. I admit that in this case a lot depends on indexing and data distribution so it may happen that it would not make any difference. It would had to be tested, as usual.

quote:
The execution plan on such a query such as this one to create 3 different stored procedures I would never take that route. You state an index on a column, and my response is if your client code is checking 3 text boxes for parameters just to figure out which condition to take to call the stored procedure outweighs one single stored procedure using simple and / or.

You would have to if I was assigned to review sql code on your project and after testing i figured out we gain performance or decrease cpu. Having more to do on the midle tier or client doesn't count as much because you can add another instance of dll and load balance them all. Database is the least scalable component of multi-tier application.

quote:
8000 page hits per second, you work for google?

I guess Google has even higher trafic then Monster. Btw, I don't work for Monster anymore.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-19 : 03:12:43

quote:
mmarovic, your example does work and since we don't have that much records to retreive (about 200 records on the max), performance is no issue.

Performance depends even more on how many rows are in the table. As I said before it may happen than alternative solution with 2-3 procs wouldn't make any difference. But it looks like you don't need such extreme optimization, so go ahead with whatever you find fits your needs the best.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources