CODEWITHSUNDEEP
t-sqlstored-proceduressql-server-2012
EverPresent
EverPresent

Reputation: 1970

Need an IF-ELSE inside a CASE-WHEN choice

I've got an existing stored procedure that looks like this:

SELECT TOP 1
...
    CASE PX.Client.value('(Type)[1]', 'varchar(25)')
      WHEN 'Sole Proprietor' THEN 'SPR'
      WHEN 'Partnership' THEN 'PAR'
      WHEN 'Corp (C)' THEN 'COR'
      WHEN 'Corp (S)' THEN 'SCO'
      WHEN 'LimitedCorp' THEN 'LLC'
      WHEN 'Trust' THEN 'TRU'
    END AS MyStructure,
...
INTO #Prod 
FROM 
  @XMLIN.nodes('/MyDataset/MyRegistration') AS PX(Client)

Everything works great except I need to change the logic. Right now if Type is "LimitedCorp" then MyStructure is always "LLC". But I need to change it so that it sub-divides MyStructure based on the TaxClassification like so (pseudo-code):

...
WHEN 'LimitedCorp' THEN 
    IF PX.Client.value('(TaxClassification)[1]') == 'Partnership' THEN 'LP'
    ELSE IF PX.Client.value('(TaxClassification)[1]') == 'SCorp' THEN 'LLS'
    ELSE 'LLC'
WHEN 'Trust' ...

In my .NET code this would be a piece of cake! I'm not very strong on my stored procedure syntax though! Thanks!

Upvotes: 1

Views: 73

Answers (1)

canon
canon

Reputation: 41685

Nest another case:

WHEN 'LimitedCorp' THEN 

    case 
       when PX.Client.value('(TaxClassification)[1]') == 'Partnership' then 'LP'
       when PX.Client.value('(TaxClassification)[1]') == 'SCorp' then 'LLS'
       else 'LLC'
    end

WHEN 'Trust' THEN 'TRU'

Upvotes: 2

Related Questions