CODEWITHSUNDEEP
sql-servercase
windowsgm
windowsgm

Reputation: 1616

SQL Case Issue Setting Variable

Why is it that I can't do the below script and how would I go about fixing it? I get the error Inccorect syntax near '='. I was doing it using ...= 1 THEN 'D' ELSE '' END as Aset but I need to check Aset and Bset in the third Case so I declared Aset and Bset instead.

declare @Aset varchar(10)
declare @Bset varchar(10)
UPDATE m
    SET m.new_name = m.new_name + ' ' + @ASet + AndSet + @BSet + ' Type'
FROM contactMaster m
inner join contact c on 
    m.contactid = c.contactid
     CROSS APPLY (
         SELECT CASE @Aset WHEN (c.category1|
                            c.category2|
                            c.category3|
                            c.category4) = 1 THEN 'C' ELSE '' End
               ,CASE @Bset WHEN (c.category5|
                            c.category6|
                            c.category7|
                            c.category8) = 1 THEN 'D' ELSE '' END
                ,CASE WHEN @BSet = 'D' and  @ASet  = 'C' THEN ' & ' ELSE '' END AS AndSet
     ) AS CA1

Upvotes: 0

Views: 36

Answers (1)

Code Maverick
Code Maverick

Reputation: 20415

I don't have your table schema or data, so I had to wing it and couldn't test it, but see if this might work:

UPDATE      m
SET         m.new_name = m.new_name 
                         + ' ' 
                         + CA1.ASet 
                         + CASE WHEN 
                           (
                                CA1.Aset = 'C' AND 
                                CA1.Bset = 'D' 
                           ) 
                           THEN ' & ' ELSE '' END 
                         + CA1.BSet + ' Type'

FROM        contactMaster   m
JOIN        contact         c on m.contactid = c.contactid
CROSS APPLY (
                SELECT  CASE WHEN 
                        (
                            c.category1 |
                            c.category2 |
                            c.category3 |
                            c.category4
                        ) 
                        = 1 THEN 'C' ELSE '' END AS Aset,
                        CASE WHEN 
                        (
                            c.category5 |
                            c.category6 |
                            c.category7 |
                            c.category8
                        ) 
                        = 1 THEN 'D' ELSE '' END AS Bset
            ) AS CA1

Upvotes: 1

Related Questions