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

Transact-SQL (2005)

Handlign case statement

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-25 : 18:45:15

Hi,
I am developing a stored procedure. It is just in the initial stage. I have a case statement in the code. The case statement is such that if parameter value 999 is chosen then all rows corresponding field reportnbr should be the condition, if 1000 is chosen then rows corresponding to value of field reportnbr >=42 should be in the condition else the value corresponding to the parameter should be chosen for the field reportnbr(in the condition). I am haivng difficulty to construct the second scenario. Any help is apprecited. Thanks in advance. Regards:

CODE:
CREATE proc determine_reports_rights
@reportnumber varchar(4)
As

SET NOCOUNT ON
--Get the usertype and reportnbr FROM dbo_reports in a temporary table
create table #ReportTable1(
UserType varchar(8) ,
ReportNbr int)

insert into #ReportTable1(UserType, ReportNbr)
select usertype, reportnbr from dbo_reports
-- where reportnbr like @reportnumber
where reportnbr = CASE
WHEN @reportnumber = 999 then reportnbr -- Takes care of all the rows
WHEN @reportnumber = 1000 then -- need to put an expression wher the reportnbr field will have value of >=42
else @reportnumber
end
order by reportnbr

-- select * from #ReportTable1

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-06-26 : 00:09:37

Try this:

select usertype, reportnbr from dbo_reports
WHERE reportnbr = CASE
WHEN @reportnumber IN (999, 1000) THEN reportnbr
ELSE @reportnumber
END
AND reportnbr >= CASE
WHEN @reportnumber = 1000 THEN 42
ELSE reportnbr
END

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-26 : 09:54:13

Thanks for your help. I appreciate it. Actually when I will choose 1000 then rows corresponding to reportnbr 42, 43, 44 and 45 should be in the display. With the code you are recommending only rows corresponding to reportnbr 42 will be displayed. How do you bring all four of the above. Any further thoughts.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 10:03:34

Did you try this?

select usertype, reportnbr from dbo_reports
where reportnbr = CASE
WHEN @reportnumber = 999 then reportnbr -- Takes care of all the rows
WHEN @reportnumber = 1000 and reportnbr >=42 then reportnbr -- need to put an expression wher the reportnbr field will have value of >=42
else @reportnumber
end
order by reportnbr

Madhivanan

Failing to plan is Planning to fail

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-26 : 10:21:39

Hi Madhivanan,
I tried working on the code as per your suggestion and it worked perfect. Thanks a lot for your help. I appreciate it. Regards.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 10:34:25

quote:
Originally posted by gamaz

Hi Madhivanan,
I tried working on the code as per your suggestion and it worked perfect. Thanks a lot for your help. I appreciate it. Regards.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources