Case statement in where clause based on paramter

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

Transact-SQL (2005)

Case statement in where clause based on paramter

Author

Topic

carriehoff
Starting Member

29 Posts

Posted - 2011-05-04 : 14:42:00

Hi all,

I have this query:


select field1, field2, field3 from tablename 
where fieldname = Case when @parameter = 0 then 1 end

Seems to work fine, but what I'm looking for is a query that will find all the rows where fieldname = 1 when @parameter = 1 OR, if @paramater = 0 then I want fieldname = 0 OR 1.

Fieldname is a bit field, and I only want to include certain records if the @paramater = 1. If the parameter = 0 then I want all of the records.

I hope that makes sense. Can anybody help?

Thanks,
Carrie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-04 : 14:54:15

select field1, field2, field3 from tablename
where @parameter = 0 OR fieldname = 1

carriehoff
Starting Member

29 Posts

Posted - 2011-05-04 : 18:39:21

That was awesome - so simple, ashamed I overlooked it.

How about this, same type of query but . . .

If a bit paramter is true then I want to exclude a field from the select list and group by clause, without using dynamic SQL of course.


IF @bitfield = 1 
--do this
select field1, field2, field3 from tablename 
where fieldname = Case when @parameter = 0 then 1 end
group by field1, field2, field3

--else do this
select field1, field2 from tablename
where fieldname = Case when @parameter = 0 then 1 end
group by field1, field2

I realize, of course, I could just write the query out twice excluding the field on the given condition, but the query is actually quite a bit longer than I have shown in this example - and so if there is an easier way, I'm all ears!

Thank you,
Carrie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-04 : 20:46:58

No, that's the only way to do that without dynamic SQL.

Why not just return the same columns? Unless you're feeding another table and have to match its columns, you might as well return field3 for both values of @bitfield. You can always hide an extra column on a report.

carriehoff
Starting Member

29 Posts

Posted - 2011-05-05 : 08:08:46

True and thank you, but adding a field to the group by clause changes the resultset signigicantly.

I read that dynamic SQL should be avoided whenever possible, but in this case, if the parameters are merely determining the coniditions, and whether or not fieldnames are included (but not sending in data directly), wouldn't it be the best solution?

Thanks,
Carrie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-05 : 09:45:16

Don't know about "best", but as long as you protect against SQL injection dynamic SQL may work better for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources