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

Transact-SQL (2008)

Case in Where Clause

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-20 : 03:02:26

One of the conditions in my query is like below and I used Case but result not coming correct
Can anybody help ?

If @param is null then take all records
IF @param is 0 then take records where columnA Is NULL
IF @param is not 0 AND not null then Get records where columnA = @param

Thanks..

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-20 : 03:19:29

I will try something like this:



WHERE

(@param Is null AND  1=1)
or
(@param = 0 AND ColumnA Is Null)
or
(@param is Not null AND @param <> 0 and columnA=@param)

sabinWeb MCP

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-20 : 17:07:26

[code]

WHERE
((@param IS NULL) OR (@param = 0 AND ColumnA IS NULL) OR (@param <> 0 AND columnA = @param))

[/code]

adsingh82
Starting Member

20 Posts

Posted - 2014-05-21 : 05:36:22

Try this out

WHERE ISNULL(ColumnA, 0) = CASE WHEN @param IS NULL THEN ColumnA
WHEN @Param = 0 THEN 0
WHEN @Param <> 0 THEN @param END

Regards,
Alwyn.M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources