CODEWITHSUNDEEP
sqlsql-servert-sqlcase
user5021612
user5021612

Reputation:

CASE statement construction with parameter

Can somebody please tell me, what's wrong with the following construction?:

 CASE @CountryID
        WHEN 1 THEN SET @String = REPLACE(@String,'+420','')
        WHEN 2 THEN SET @String = REPLACE(@String,'+421','')    
        WHEN 3 THEN SET @String = REPLACE(@String,'+359','')    
        WHEN 4 THEN SET @String = REPLACE(@String,'+7','')  
        WHEN 5 THEN SET @String = REPLACE(@String,'+48','') 
        WHEN 7 THEN SET @String = REPLACE(@String,'+63','') 
    END

@CountryID is INT.

The problem is appereantly in the CASE statement construction, as IF statement works well, but I can't see anything worng and tried different types of case construction and searched for documentation.

Any hints would be appreciate.

Upvotes: 1

Views: 255

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

You need to use:

SELECT @String = 
CASE @CountryID
        WHEN 1 THEN REPLACE(@String,'+420','')
        WHEN 2 THEN REPLACE(@String,'+421','')    
        WHEN 3 THEN REPLACE(@String,'+359','')    
        WHEN 4 THEN REPLACE(@String,'+7','')  
        WHEN 5 THEN REPLACE(@String,'+48','') 
        WHEN 7 THEN REPLACE(@String,'+63','') 
        -- ELSE @String  -- to avoid NULL if no country found
    END;

CASE is expression, but you used it as CASE statement (available for example in Oracle/DB2)

Oracle supports both CASE statement(not available in T-SQL):

CASE
    WHEN jobid = 'PU_CLERK' THEN sal_raise := .09;
    WHEN jobid = 'SH_CLERK' THEN sal_raise := .08;
    WHEN jobid = 'ST_CLERK' THEN sal_raise := .07;
    ELSE sal_raise := 0;
  END CASE;

and CASE expression:

 appraisal :=
      CASE grade
         WHEN 'A' THEN 'Excellent'
         WHEN 'B' THEN 'Very Good'
         WHEN 'C' THEN 'Good'
         WHEN 'D' THEN 'Fair'
         WHEN 'F' THEN 'Poor'
         ELSE 'No such grade'
      END;

EDIT:

As HABO mentioned you could rewrite it as:

SELECT @String = REPLACE(@String, 
    CASE @CountryID
            WHEN 1 THEN '+420'
            WHEN 2 THEN '+421' 
            WHEN 3 THEN '+359'  
            WHEN 4 THEN '+7'
            WHEN 5 THEN '+48'
            WHEN 7 THEN '+63'
            ELSE ''
        END, '');

Alternatively in SQL Server 2012 and above you could use CHOOSE:

SELECT @String = 
  REPLACE(@String, CHOOSE(CountryId,'+420','+421','+359','+7','+48', '+63'), '')

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270371

@lad2025 has the rewrite of the code that works (and a good explanation!). I want to point out that in SQL Server, I would prefer:

SELECT @String = REPLACE(@String, v.str, '')
FROM (VALUES (1, '+420'), (2, '+421'), (3, '+359'), (4, '+7'),
             (5, '+48'), (7, '+63')
     ) v(countryId, str)
WHERE v.countryId = @CountryID;

I don't think this will change the value of @String if there is no match.

Upvotes: 2

Related Questions