CASE Statement in a SQL Procedure - IBM DB2 to SQL Server Migration

In DB2 stored procedure you can use a standalone CASE statement (do not confuse with CASE expressions inside SELECT statements i.e.)

Microsoft SQL Server does not support CASE statements in Transact-SQL (although it supports CASE expressions in SQL statements like SELECT), so you have to use IF statement instead.

DB2:

  --#SET TERMINATOR /
 
  -- A sample procedure using standalone CASE statement (not CASE expression)
  CREATE PROCEDURE case_stmt1 (param1 VARCHAR(30))
  P1: BEGIN
    DECLARE s VARCHAR(1000); 
    CASE param1 
      WHEN 'a' THEN SET s = 'a';
      WHEN 'b' THEN SET s = 'b';
    END CASE;
  END P1
  /

SQL Server:

  -- We have to use IF statement
  CREATE PROCEDURE case_stmt1 ( @param1 VARCHAR(30))
  AS
   BEGIN
     SET NOCOUNT ON;
     DECLARE @s VARCHAR(1000); 
     IF @param1 = 'a' SET @s = 'a';
     ELSE IF @param1 = 'b' SET @s = 'b';
  END
  GO

For more information, see IBM DB2 to SQL Server Migration.