Need help in cAse statement - 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 2008 Forums

Transact-SQL (2008)

Need help in cAse statement

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-08-09 : 06:45:19

I have a table which have nullable bollean column.
In my stored procedure I am passing a parameter which will have string 'True' or 'False'.
Waht i wanted is that when i provide False in my parameter i should get the result of the rows where i have either null or false in that particular field.
My current query is like as follow

Declare @IsPerishable as varchar(4)

SELECT CON_SHIPPER_CONTENT_ID
,IsPerishable
FROM MDB_CONTENT_INFO
WHERE ( IsPerishable = CASE WHEN @IsPerishable = 'True' THEN 1
WHEN @IsPerishable ='False' THEN 0 Or isnull

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-09 : 07:00:48

Its not possible in static query like this
You need to use if statement instead of case like below if you dont want to use dynamic query-


Declare @IsPerishable as varchar(5)
SET @IsPerishable = 'TRUE'

IF @IsPerishable = 'FALSE'
SELECT CON_SHIPPER_CONTENT_ID ,IsPerishable FROM MDB_CONTENT_INFO
WHERE IsPerishable = 0 OR IsPerishable IS NULL
ELSE IF @IsPerishable = 'TRUE'
SELECT CON_SHIPPER_CONTENT_ID ,IsPerishable FROM MDB_CONTENT_INFO
WHERE IsPerishable = 1

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-09 : 07:08:38

For dynamic query -


DECLARE @IsPerishable AS VARCHAR(5)
SET @IsPerishable = 'TRUE'
DECLARE @SQL AS VARCHAR(1000)

SET @SQL = 
'SELECT CON_SHIPPER_CONTENT_ID ,IsPerishable FROM MDB_CONTENT_INFO WHERE IsPerishable IN ' + 
CASE @IsPerishable WHEN 'FALSE' THEN '(0,NULL)' WHEN 'TRUE' THEN '(1)' END 

EXECUTE(@SQL)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kokkula
Starting Member

41 Posts

Posted - 2010-08-09 : 07:30:45

Hello,

Try this....

Declare @IsPerishable as varchar(4)

SELECT CON_SHIPPER_CONTENT_ID
,IsPerishable
FROM MDB_CONTENT_INFO
WHERE ISNULL(IsPerishable,0) = (CASE WHEN @IsPerishable = 'True' THEN 1
ELSE 0 END)

Hope its helpful....

Pavan
Infosys Technologies Limited

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources