Stanislav Jirák Stanislav Jirák - 3 years ago 247
SQL Question

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','')


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.

Answer Source

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

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):

    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;

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'


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'), '')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download