need help with a case query - 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 2005 Forums

Transact-SQL (2005)

need help with a case query

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-01-21 : 01:08:08

Hi All,
following is the query:

declare @groupfindid int
set @groupfindid = 3

declare @tbl1 TABLE
(rowid int
,rowname varchar(10)
,groupid int)

insert into @tbl1
select 100,'view 1',10 union all
select 200,'view 2',20 union all
select 300,'view 2',20 union all
select 400,'view 1',10 union all
select 500,'view 2',20 union all
select 600,'view 2',20 union all
select 700,'view 1',10

select * from @tbl1 where groupid =
CASE WHEN @groupfindid = 1 then 10
WHEN @groupfindid = 2 then 20
--WHEN @groupfindid = 3 then ???? --ISSUE HERE
END

@groupfindid = 1 displays records with groupid = 10
@groupfindid = 2 displays records with groupid = 20
@groupfindid = 3 displays ALL records

i m not understanding what condition should i be putting in '@groupfindid = 3' so that all records are retrieved

thnx :)

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-21 : 01:11:43

declare @groupfindid int
set @groupfindid = 3

declare @tbl1 TABLE
(rowid int
,rowname varchar(10)
,groupid int)

insert into @tbl1
select 100,'view 1',10 union all
select 200,'view 2',20 union all
select 300,'view 2',20 union all
select 400,'view 1',10 union all
select 500,'view 2',20 union all
select 600,'view 2',20 union all
select 700,'view 1',10

select * from @tbl1 where groupid =
CASE WHEN @groupfindid = 1 then 10
WHEN @groupfindid = 2 then 20
WHEN @groupfindid = 3 then groupid--Use this
END

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 01:16:23

declare @groupfindid int
set @groupfindid = 1
select * from @tbl1
where groupid =
CASE WHEN @groupfindid= 1 then 10
WHEN @groupfindid = 2 then 20
else groupid
END

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-21 : 01:21:35

quote:
Originally posted by bklr

declare @groupfindid int
set @groupfindid = 1
select * from @tbl1
where groupid =
CASE WHEN @groupfindid= 1 then 10
WHEN @groupfindid = 2 then 20
else groupid
END

With this solution, if @groupfindid is other than 1 and 2, then it will return all records. What we require is when @groupfindid = 3 then ALL records should be returned

Rahul Shinde

zion99
Posting Yak Master

141 Posts

Posted - 2009-01-21 : 01:28:48

aarghh...how very foolish of me...
thanks to both of you