Stanislav Jirák Stanislav Jirák - 6 months ago 73
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','')
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.

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