CODEWITHSUNDEEP
sqlsql-server
satyajit
satyajit

Reputation: 2700

Use of CASE in WHERE clause

Below is the code:

CREATE PROCEDURE [dbo].[PR_CDMS_ADMIN_GET_SECURITY_INFO] (    
 DECLARE @chvMajorCatgry varchar(50) 
 DECLARE @chvMinorCatgry varchar(50)   

)    
AS        

if (@chvMajorCatgry = 'yyy')
    begin
        select caf.ACTN_FACT_SID            
        from CDMS_ACTN_FACT caf     
        where caf.ACTN_SID in (select ACTN_SID from CDMS_ACTN where ACTN_CATGRY_CD = 'yyy') 
        print @chvMajorCatgry   
    end
    print @chvMajorCatgry
else if (@chvMajorCatgry = 'zzz')
    begin 
        select  caf.ACTN_FACT_SID           
        from CDMS_ACTN_FACT caf     
        where caf.ACTN_SID = (select ACTN_SID from CDMS_ACTN where ACTN_CATGRY_CD = 'zzz' and ACTN_CD = @chvMinorCatgry)        
    end

How do I handle this to have a single select statement?

Upvotes: 1

Views: 186

Answers (2)

WT_W
WT_W

Reputation: 235

Please find below my suggestion. You may need to change the wildcard at the end of the case statement in the subquery depending on what datatype the ACTN_CD column is. 

CREATE PROCEDURE [dbo].[PR_CDMS_ADMIN_GET_SECURITY_INFO] (    
  @chvMajorCatgry varchar(50),
 @chvMinorCatgry varchar(50)   
 --SET @chvMajorCatgry = 'Account'
)    
AS      

select caf.ACTN_FACT_SID            
from CDMS_ACTN_FACT caf     
where caf.ACTN_SID in (
                       select ACTN_SID 
                       from CDMS_ACTN 
                       where ACTN_CATGRY_CD = @chvMajorCatgry
                       and ACTN_CD like case @chvmajorcatgry when 'Attribute' then @chvMinorCatgry else '%' end)

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85046

Bleh, that is rough. Try this:

    select  caf.ACTN_FACT_SID           
    from CDMS_ACTN_FACT caf     
    where caf.ACTN_SID IN (
               select ACTN_SID 
               from CDMS_ACTN 
               where ( 
                       @chvMinorCatgry = 'zzz' 
                       AND ACTN_CATGRY_CD = 'Attribute' 
                       AND ACTN_CD = @chvMinorCatgry)
                     )
               OR    (
                       @chvMinorCatgry = 'yyy' 
                       AND ACTN_CATGRY_CD = 'Account'
                     )
             )

Upvotes: 2

Related Questions