title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | monikerRange |
---|---|---|---|---|---|---|---|---|
CASE expression in a natively compiled stored procedure |
Natively compiled T-SQL modules support CASE expressions in some versions of SQL Server. This example implements the CASE expression in a query. |
MikeRayMSFT |
mikeray |
11/21/2017 |
sql |
in-memory-oltp |
conceptual |
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
Applies to: [!INCLUDE ssazure-sqldb] and SQL Server starting [!INCLUDE sssql17-md]
CASE expressions are supported in natively compiled T-SQL modules. The following example demonstrates a way to use the CASE expression in a query.
-- Query using a CASE expression in a natively compiled stored procedure.
CREATE PROCEDURE dbo.usp_SOHOnlineOrderResult
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE=N'us_english')
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
END
GO
EXEC dbo.usp_SOHOnlineOrderResult
GO
Applies to: [!INCLUDEssSQL14-md] and SQL Server starting [!INCLUDEssSQL15-md]
CASE expressions are not supported in natively compiled T-SQL modules. The following sample shows a way to implement the functionality of a CASE expression in a natively compiled stored procedure.
The code samples uses a table variable to construct a single result set. This is suitable only when processing a limited number of rows, because it involves creating an additional copy of the data rows.
You should test the performance of this workaround.
-- 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, N'Order 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
Migration Issues for Natively Compiled Stored Procedures
Transact-SQL Constructs Not Supported by In-Memory OLTP