实现 CASE 语句
本机编译的存储过程中不支持 case 语句。 以下示例演示一种在本机编译的存储过程中实现 case 语句功能的方法。
该示例使用表变量构造单个结果集,该结果集仅当处理有限数量的行时才适用,因为它涉及创建数据行的额外副本。
应测试此解决方法的性能,以确保它在应用程序中按预期方式执行。
-- 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