CASE statements

Question

CASE statements

Tara-1044200

SSCoach

Points: 15951
More actions
May 26, 2009 at 12:48 pm

#137982

I am not sure if i have to use CASE or IF but here is the requirement.
I have a ssrs report where it has 3 drop down list for
1.DBname
2.Year
3.Month
How do i populate report with switch cases where it should also accept nulls if nothing is selected from the drop down list.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tara-1044200 SSCoach Points: 15951 More actions · Answer 1

I think my question was is not clear, let me explain it.

I have 3 parameters for my store proc and i would like an IF or CASE statements to select these parameters in all possible probabilities, like

1. if @month='' and @year='' and @dbname='' , it has to select all records

2. if @month='' and @year'' and @dbname=''.then select * .....where year=@year

3. if @month'' and @year='' and @dbname='' then select * ..where month=@month and dbname=@dbname

4. and so on.....

it has to satisfy all possible probabilities.

Is there any easy way to do so....

mverma4you Ten Centuries Points: 1013 More actions · Answer 2

allow null value from the report parameter and add logic in the SP or TSQL like ......... WHERE (@DBNAME IS NULL OR DBNAME = @DBNAME)

this way if you passed the null value from the report parameter it will return all the record or if any DBNAME is passed then it will pull the databased on filter condition.

pushpa.kumari SSC Veteran Points: 298 More actions · Answer 3

Hi,

Try this, please remember to cast your parameter.

select * from tablename

where monthColumnName = case when @month='' then monthColumnName else @month end

AND

yearColumnName = case when @year='' then yearColumnName else @year end

AND

dbname = case when @dbname='' then dbname else @dbname end

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

Tara (5/27/2009)
I think my question was is not clear, let me explain it.
I have 3 parameters for my store proc and i would like an IF or CASE statements to select these parameters in all possible probabilities, like
1. if @month='' and @year='' and @dbname='' , it has to select all records
2. if @month='' and @year'' and @dbname=''.then select * .....where year=@year
3. if @month'' and @year='' and @dbname='' then select * ..where month=@month and dbname=@dbname
4. and so on.....
it has to satisfy all possible probabilities.
Is there any easy way to do so....

suggestion:

select @month = nullif(@month,''), @year = nullif(@year,''), @dbname = nullif( @dbname,'')

...

select ...

from ...

where month = isnull(@month,month)

and year = isnull( @year, year )

and dbname = isnull( @dbname, dbname )

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

yes i did get the result i need but i would like to add 1 more functionality to it.

how would i compare from one parameter to other like say..

compare between server1 and server2/ dbname1 and dbname2 if they are matching.

Wadingman SSC-Addicted Points: 466 More actions · Answer 6

We use this to make sure we are in DEV

before we call a stored procedure that deletes data

use master

go

declare @DEV_sysserver_srvname varchar (20)

select @DEV_sysserver_srvname = (select srvname from sysservers

where srvname = 'yourservername')

select @DEV_sysserver_srvname

IF (select srvname from sysservers where srvname = 'yourservername') = @dev_sysserver_srvname