If Statement inside Select 2

[jebushatescats]

Hello all
I have the following stored procedure

ALTER PROCEDURE dbo.hrld_sel_business_units
(@PROCESS_LEVEL_LOW varchar(5) = null,
@PROCESS_LEVEL_HIGH varchar(5)= null)
AS

SELECT
'0' as 'PROCESS_LEVEL',
'<All>' as 'NAME'

UNION

SELECT
p.PROCESS_LEVEL,
p.NAME

FROM
DBLAWTS1..PRSYSTEM p
WHERE
p.PROCESS_LEVEL NOT IN ('010', '011', '012') and
p.PROCESS_LEVEL >= @PROCESS_LEVEL_LOW and
p.PROCESS_LEVEL <= @PROCESS_LEVEL_HIGH

ORDER BY
P.NAME


I want to be able to put a conditon on the
'<All>' as 'NAME' statement
so that Name is only assigned the value all
when @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH

Something like this

ALTER PROCEDURE dbo.hrld_sel_business_units
(@PROCESS_LEVEL_LOW varchar(5) = null,
@PROCESS_LEVEL_HIGH varchar(5)= null)
AS

SELECT
'0' as 'PROCESS_LEVEL',
IF @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH
'<All>' as 'NAME'
END

UNION

SELECT
p.PROCESS_LEVEL,
p.NAME

FROM
DBLAWTS1..PRSYSTEM p
WHERE
p.PROCESS_LEVEL NOT IN ('010', '011', '012') and
p.PROCESS_LEVEL >= @PROCESS_LEVEL_LOW and
p.PROCESS_LEVEL <= @PROCESS_LEVEL_HIGH

ORDER BY
P.NAME

 

[AlexCuse]

Is there something you want to assign if process level low <> process level high, or do you want to exclude that row entirely?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jebushatescats]

AlexCuse

I want to exclude the row entirely
if process level low <> process level high,

 

[gmmastros]

Just put that as a filter condition on the other select, like this...

[COLOR=blue]ALTER[/color]      [COLOR=blue]PROCEDURE[/color] dbo.hrld_sel_business_units
         (@PROCESS_LEVEL_LOW [COLOR=blue]varchar[/color](5) = null,       
          @PROCESS_LEVEL_HIGH [COLOR=blue]varchar[/color](5)= null)       
[COLOR=blue]AS[/color] 

[COLOR=blue]SELECT[/color]
    [COLOR=red]'0'[/color]    [COLOR=blue]as[/color] [COLOR=red]'PROCESS_LEVEL'[/color],
    [COLOR=blue]IF[/color] @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH
       [COLOR=red]'<All>'[/color]    [COLOR=blue]as[/color] [COLOR=red]'NAME'[/color]

UNION

[COLOR=blue]SELECT[/color]
       p.PROCESS_LEVEL,
       p.NAME

[COLOR=blue]FROM[/color]
       DBLAWTS1..PRSYSTEM p
[COLOR=blue]WHERE[/color]
       p.PROCESS_LEVEL NOT IN ([COLOR=red]'010'[/color], [COLOR=red]'011'[/color], [COLOR=red]'012'[/color])  and
       p.PROCESS_LEVEL >= @PROCESS_LEVEL_LOW and      
       p.PROCESS_LEVEL <= @PROCESS_LEVEL_HIGH      
       [!]@PROCESS_LEVEL_LOW <> @PROCESS_LEVEL_HIGH[/!]
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color]
    P.NAME

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry. There is still a problem with the if. Try this instead.

[COLOR=blue]ALTER[/color]      [COLOR=blue]PROCEDURE[/color] dbo.hrld_sel_business_units
         (@PROCESS_LEVEL_LOW [COLOR=blue]varchar[/color](5) = null,       
          @PROCESS_LEVEL_HIGH [COLOR=blue]varchar[/color](5)= null)       
[COLOR=blue]AS[/color] 

[COLOR=blue]If[/color] 

[COLOR=blue]SELECT[/color]
    [COLOR=red]'0'[/color]    [COLOR=blue]as[/color] [COLOR=red]'PROCESS_LEVEL'[/color],
    [COLOR=red]'<All>'[/color] [COLOR=blue]As[/color] [[COLOR=blue]Name[/color]]
[COLOR=blue]Where[/color] @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH

UNION

[COLOR=blue]SELECT[/color]
       p.PROCESS_LEVEL,
       p.NAME

[COLOR=blue]FROM[/color]
       DBLAWTS1..PRSYSTEM p
[COLOR=blue]WHERE[/color]
       p.PROCESS_LEVEL NOT IN ([COLOR=red]'010'[/color], [COLOR=red]'011'[/color], [COLOR=red]'012'[/color])  and
       p.PROCESS_LEVEL >= @PROCESS_LEVEL_LOW and      
       p.PROCESS_LEVEL <= @PROCESS_LEVEL_HIGH      
       @PROCESS_LEVEL_LOW <> @PROCESS_LEVEL_HIGH       
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color]
    P.NAME

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I think that your best bet is to add a 'where' clause to your first query, based off of whether or not your two parameters are equal. We can do this using CASE, like so:

[COLOR=blue]where[/color] ([COLOR=blue]case[/color] [COLOR=blue]when[/color] @process_level_low = @process_level_high [COLOR=blue]then[/color] 1
	[COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) = 1

The full query would look like this:

[COLOR=blue]ALTER[/color]    [COLOR=blue]PROCEDURE[/color] dbo.hrld_sel_business_units
         (@PROCESS_LEVEL_LOW [COLOR=blue]varchar[/color](5) = null,
          @PROCESS_LEVEL_HIGH [COLOR=blue]varchar[/color](5)= null)
[COLOR=blue]AS[/color] 

[COLOR=blue]SELECT[/color]
    [COLOR=red]'0'[/color]    [COLOR=blue]as[/color] [COLOR=red]'PROCESS_LEVEL'[/color],
    [COLOR=red]'<All>'[/color]    [COLOR=blue]as[/color] [COLOR=red]'NAME'[/color]
[COLOR=blue]where[/color] ([COLOR=blue]case[/color] [COLOR=blue]when[/color] @process_level_low = @process_level_high [COLOR=blue]then[/color] 1
	[COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) = 1

UNION

[COLOR=blue]SELECT[/color]
    p.PROCESS_LEVEL,
    p.NAME

	

[COLOR=blue]FROM[/color]
    DBLAWTS1..PRSYSTEM p
[COLOR=blue]WHERE[/color]
    p.PROCESS_LEVEL NOT IN ([COLOR=red]'010'[/color], [COLOR=red]'011'[/color], [COLOR=red]'012'[/color])  and
       p.PROCESS_LEVEL >= @PROCESS_LEVEL_LOW and  
       p.PROCESS_LEVEL <= @PROCESS_LEVEL_HIGH     

[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color]
    P.NAME

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Where @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH

Ignorance of certain subjects is a great part of wisdom

 

[jebushatescats]

Thanks for the replies, I will play with it and let you know the results

 

[jebushatescats]

AlexCuse

That did it thanks!!

 

[AlexCuse]

Hey, jebushatescats

You really should use what George suggested:

Where @PROCESS_LEVEL_LOW = @PROCESS_LEVEL_HIGH

The query I posted was unnecessarily complex. I have been trying to minimize my caffeine intake and apparently its' having some side effects ;-)

Glad it worked though (so side effects not too severe)

Ignorance of certain subjects is a great part of wisdom