Export (0) Print
Expand All

Implementing a CASE Statement

SQL Server 2014

Case statements are not supported in natively compiled stored procedures. The following sample shows a way to implement the functionality of a case statement in a natively compiled stored procedure.

The samples uses a table variable to construct a single result set, which is only suitable when processing a limited number of rows, as it involves creating an additional copy of the data rows.

You should test the performance of this workaround, to be sure that it performs as expected in your application.

-- original query
SELECT 
   SalesOrderID, 
   CASE (OnlineOrderFlag) 
   WHEN 1 THEN N'Order placed online by customer'
   ELSE N'Order placed by sales person'
   END
FROM Sales.SalesOrderHeader_inmem


--  workaround for CASE in natively compiled stored procedures
--  use a table for the single resultset
CREATE TYPE dbo.SOHOnlineOrderResult AS TABLE
(
   SalesOrderID uniqueidentifier not null index ix_SalesOrderID,
     OrderFlag nvarchar(100) not null
) with (memory_optimized=on)
go

-- natively compiled stored procedure that includes the query
CREATE PROCEDURE dbo.usp_SOHOnlineOrderResult
   WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
   AS BEGIN ATOMIC WITH
      (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE=N'us_english')

   -- table variable for creating the single resultset
   DECLARE @result dbo.SOHOnlineOrderResult

   -- CASE OnlineOrderFlag=1
   INSERT @result 
   SELECT SalesOrderID, N'Order placed online by customer'
      FROM Sales.SalesOrderHeader_inmem
      WHERE OnlineOrderFlag=1

   -- ELSE
   INSERT @result 
   SELECT SalesOrderID, placed by sales person'
      FROM Sales.SalesOrderHeader_inmem
      WHERE OnlineOrderFlag!=1

   -- return single resultset
   SELECT SalesOrderID, OrderFlag FROM @result
END
GO

EXEC dbo.usp_SOHOnlineOrderResult
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft