Post reply

CASE statement in where clause

  • October 29, 2014 at 4:26 pm

    #397583

    Hi,

    I have the following query and want to use case statement in where clause as per below.

    Can anyone help on this??

    declare @CODE varchar(32);

    declare @Work varchar(32);

    SET @Work = 'CASUAL'

    SET @CODE = 1000

    SELECT * from Business A

    INNER JOIN Master B on A.id = B.id

    WHERE A.TYPE = 'PURCHASE'

    AND B.CLASS ='@Work'

    -----------------------------------------

    and CASE @Work

    WHEN 'CASUAL' THEN B.CSL =@CODE

    WHEN 'PERMANENT' THEN B.PRM = @CODE

    END

    -------------------------------------------

  • October 29, 2014 at 6:25 pm

    #1755659

    I'm leaving you with 3 options. The third might be the best, but you have to test them.

    declare @CODE varchar(32);

    declare @Work varchar(32);

    SET @Work = 'CASUAL'

    SET @CODE = 1000

    --Option 1

    SELECT * from Business A

    INNER JOIN Master B on A.id = B.id

    WHERE A.TYPE = 'PURCHASE'

    AND B.CLASS ='@Work'

    and (( @Work = 'CASUAL' AND B.CSL =@CODE)

    OR @Work = 'PERMANENT' AND B.PRM = @CODE))

    --Option 2

    IF @Work = 'CASUAL'

    SELECT * from Business A

    INNER JOIN Master B on A.id = B.id

    WHERE A.TYPE = 'PURCHASE'

    AND B.CLASS ='@Work'

    and B.CSL =@CODE

    ELSE

    IF @Work = 'PERMANENT'

    SELECT * from Business A

    INNER JOIN Master B on A.id = B.id

    WHERE A.TYPE = 'PURCHASE'

    AND B.CLASS ='@Work'

    and B.PRM =@CODE

    --Option 3 (Possibly the best)

    DECLARE @SQL nvarchar(300)

    SELECT @SQL = 'SELECT * from Business A

    INNER JOIN Master B on A.id = B.id

    WHERE A.TYPE = ''PURCHASE''

    AND B.CLASS =''@Work''

    and B.' + CASE @Work WHEN 'CASUAL' THEN 'CSL'

    WHEN 'PERMANENT' THEN 'PRM' END + ' =@CODE;'

    --PRINT @SQL

    EXEC sp_executesql @SQL, N'@CODE varchar(32)', @CODE

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 29, 2014 at 10:16 pm

    #1755678

    Thanks , I have used 1st method!!

Viewing 3 posts - 1 through 2 (of 2 total)

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