if statement 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 2012 Forums

Transact-SQL (2012)

if statement in where clause

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-07 : 15:40:27

I know this is wrong, but how do I do this?

declare @archived bit = null
declare @onlyRespond bit = 1

SELECT id, name, description, archived
FROM dbo.tblTrkAction t
where (@archived is null or t.archived = @archived)
CASE @onlyRespond
when @onlyRespond = 1 then
and t.Id in (108,124,98,95,96,99,126,90,89,115)
END
order by t.Name

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 15:52:14

I didn't quite follow the logic you want to implement - but may be this:

DECLARE @archived BIT = NULL
DECLARE @onlyRespond BIT = 1


SELECT id, name, description, archived
FROM dbo.tblTrkAction t
where (@archived is null or t.archived = @archived)
AND 
(
	(t.Id in (108,124,98,95,96,99,126,90,89,115) AND @onlyRespond = 1)
	OR
	(@onlyRespond <> 1)
)
order by t.Name

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-07 : 15:55:53

@onlyRespond can = 0 for false, 1 for true, or can be null

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 16:05:37

What should happen in each case?
If @onlyRespond = 1 return data for t.Id in (108,124,98,95,96,99,126,90,89,115)
For the other two cases, return no data at all, or all the data, or something else?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 00:25:23

i think what you're looking at is this


declare @archived bit = null
declare @onlyRespond bit = 1


SELECT id, name, description, archived
FROM dbo.tblTrkAction t
where	(@archived is null or t.archived = @archived)
and (t.Id in (108,124,98,95,96,99,126,90,89,115)
or COALESCE(@onlyRespond,0) <> 1)
order by t.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-08 : 08:01:44

Sweet Visakh16. Thanx bro

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 08:25:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources