fetching is null records from case statement where

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 2008 Forums

Transact-SQL (2008)

fetching is null records from case statement where

Author

Topic

red108
Starting Member

6 Posts

Posted - 2011-08-11 : 04:53:37

i want to fetch Category IS NULL records WHEN 'S' , im not able to get it with the following case statement

USE AdventureWorks2008R2;
GO
DECLARE @Cat VARCHAR(5)
SET @Cat='S'
SELECT ProductNumber,ProductName where Category =
CASE @Cat WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'NULL'
ELSE 'Not for sale' END,
Name FROM Production.Product
ORDER BY ProductNumber; GO
what im trying to get is the following query

DECLARE @Cat VARCHAR(5)
SET @Cat='S'
SELECT ProductNumber,ProductName where Category is null

here @Cat is my input parameter which comes from my GUI

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-11 : 05:20:05

[code]
SELECT ProductNumber
FROM Production.Product
where (@Cat = 'R' AND Category = 'Road')
OR (@Cat = 'M' AND Category = 'Mountain')
OR (@Cat = 'T' AND Category = 'Touring')
OR (@Cat = 'S' AND Category IS NULL)
ORDER BY ProductNumber
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources