Answered by:
CASE statement in where clause

Question
-
Dear All,
I am trying to write a case statement in where clause and facing some issues. I want to run a condition based on value of a variable . But my query is giving errors .My example is as follows:
DECLARE @capID int
SET @capID =(select capID from products where pkid=1)
SELECT * from products where name='ABC'
AND
CASE WHEN @capID>88 THEN
getdate() BETWEEN sales_begin AND sales_end
ENDI mean that I want to run the case statement only when @capID>88. if its less than 88 than it doesnt do anything.
- Edited by Sammy Williams Sunday, August 24, 2014 12:42 PM
Sunday, August 24, 2014 12:41 PM
Answers
-
You can do like below
DECLARE @capID int SET @capID =(select capID from products where pkid=1) SELECT * from products where name='ABC' AND (getdate() BETWEEN sales_begin AND sales_end OR @capID <= 88) END
It will work fine so far as you've a single record with pkid = 1 in products table
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Proposed as answer by Simon_HouMicrosoft contingent staff Monday, August 25, 2014 7:55 AM
- Marked as answer by Elvis Long Thursday, September 11, 2014 9:50 AM
Sunday, August 24, 2014 1:20 PM
All replies
-
Hi I believe you dont need to use case DECLARE @capID int SET @capID =(select capID from products where pkid=1) SELECT * from products where name='ABC' AND (@capID>88 AND ......) Not sure what you want to do with sales_begin and sales_end, but BETWEEN is not used correctly
Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to youSunday, August 24, 2014 12:50 PM -
You can do like below
DECLARE @capID int SET @capID =(select capID from products where pkid=1) SELECT * from products where name='ABC' AND (getdate() BETWEEN sales_begin AND sales_end OR @capID <= 88) END
It will work fine so far as you've a single record with pkid = 1 in products table
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Proposed as answer by Simon_HouMicrosoft contingent staff Monday, August 25, 2014 7:55 AM
- Marked as answer by Elvis Long Thursday, September 11, 2014 9:50 AM
Sunday, August 24, 2014 1:20 PM -
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums. Ignorance is fine, but why do you have bad manners? The rules are posted at the front of the forum!
>> I am trying to write a CASE statement in WHERE clause and facing some issues. <<
The first issue is that SQL has no CASE statement! We have a CASE expression; you never read any book on SQL, have you? Almost everything wrong. :(
>> DECLARE @cap_id INTEGER;
SET @cap_id =(SELECT cap_id FROM Products WHERE pk_id = 1);<<
SQL is a declarative language. We do not use local variables, so @cap_id is bad code; use the expression instead. Your “pk_id” looks like a primary key of some kind, but the product_name is a nightmare; the “pk_” affix is meta-data! Remember the first week of RDBMS classes? never mix data and meta-data.
We do not use getdate() anymore; that was UNIX/Sybase in the 1980's. The
CASE is not a control flow statement! Why did you think that would work? Without any DDL or specs, here is my guess:
SELECT * -- this is bad, use a column list
FROM Products
WHERE product_name = 'ABC'
AND (CURRENT_TIMESTAMP AS DATE)
BETWEEN sales_begin_date AND sales_end_date
AND cap_id > 88;
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Sunday, August 24, 2014 2:23 PM