我是SQL Server的新手。在这里，我尝试使用CASE语句创建UDF，以根据输入的各种订单日期从数据库STRDAT获取订单状态。这是代码：
USE STRDAT
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.GetOrderStatus', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetOrderStatus ;
GO
CREATE FUNCTION dbo.GetOrderStatus(@lngOrderID int)
RETURNS varchar(50)
AS
BEGIN
WITH MyData AS
(
SELECT
ReservedDate AS Res,
ConfirmedDate AS Conf,
ProcessedDate AS Procs,
ProducedDate AS Prod,
ShippedDate AS Ship,
RefusingReason AS Refs,
CancelledDate AS Canc
FROM tbl_Order
WHERE OrderID = @lngOrderID
)
SELECT GetOrderStatus= CASE
WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL AND Ship IS NULL AND Canc IS NULL AND Refs is null
THEN 'Naujas'
WHEN NOT Canc IS NULL
THEN 'Atšauktas'
WHEN NOT Refs IS NULL
THEN 'Atmestas'
WHEN NOT Ship IS NULL
THEN 'Atkrautas'
WHEN NOT prod IS NULL
THEN 'Pagamintas'
WHEN NOT Procs IS NULL
THEN 'Apdirbtas'
WHEN NOT Conf IS NULL
THEN 'Patvirtintas'
ELSE 'N/A'
END
FROM MyData
END
这是我的第一个功能，我无法弄清楚为什么会在网上出现错误CREATE FUNTION ...：
函数中包含的Select语句无法将数据返回给客户端。
GetOrderStatus=试试这个：
RETURN (WITH MyData AS
...
SELECT CASE
...
FROM mydata);
顺便说一句：
WHEN res IS NULL AND conf IS NULL AND PROCS IS NULL AND PROD IS NULL AND Ship IS NULL AND Canc IS NULL AND Refs is null
可以更优雅地表示为：
WHEN COALESCE(res, conf, PROCS, PROD, Ship, Canc, Refs) IS NULL
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句