我正在嘗試創建一個存盤程序，我需要在其中找到給定經銷商編號的所有最新條目，然后根據某些條件獲取總數。我是這樣創建的：
CREATE PROCEDURE [dbo].[GetNotificationCount] @DealerNumber varchar(50),
@NotificationCount int OUT
AS
BEGIN
DECLARE @UserId varchar(50);
WITH Notifications (RequestId, StatusId, RequestStatusId, UserId, row_num) AS
(SELECT r.ID,
rs.StatusID,
rs.ID,
r.UserID,
ROW_NUMBER() OVER (PARTITION BY r.Id, rs.StatusID ORDER BY DateTimeStamp DESC) AS row_num
FROM Requests r
INNER JOIN RequestStatuses rs ON r.ID = rs.RequestID
WHERE r.DealerNumber = @DealerNumber)
Set @UserId = (Select Top 1 UserId from Requests where DealerNumber = @DealerNumber)
SELECT @NotificationCount = COUNT(*)
FROM Notifications n
INNER JOIN Statuses s ON n.StatusId = s.ID
WHERE n.row_num = 1
AND n.StatusId IN (SELECT ID FROM Statuses WHERE DealerPortal = 'Additional Info Needed')
AND n.RequestStatusId NOT IN (SELECT RequestStatusId FROM AdditionalInfoViewed WHERE UserId = @UserId);
END;
但我收到編譯時錯誤
SET 陳述句附近的語法不正確。
我不確定我哪里做錯了。請建議。
uj5u.com熱心網友回復：
不允許在 CTE 中使用 set 變數，您應該使用SELECT與VIEW現有規則相同的變數或填充 CTE 之外的變數
SELECT @UserId = ....
應該管用
公共表運算式中變數的規則：MSDN
uj5u.com熱心網友回復：
在 cte 開始之前設定 @UserId 值（在“with”關鍵字之前）
CREATE PROCEDURE [dbo].[GetNotificationCount]
@DealerNumber varchar(50),
@NotificationCount int out
AS
BEGIN
Declare @UserId varchar(50);
Set @UserId = (Select Top 1 UserId from Requests where DealerNumber = @DealerNumber)
;With Notifications (RequestId, StatusId, RequestStatusId, UserId, row_num)
AS
(
select r.ID, rs.StatusID, rs.ID, r.UserID, ROW_NUMBER() OVER (PARTITION BY r.Id,rs.StatusID ORDER BY DateTimeStamp DESC) as row_num
from Requests r inner join RequestStatuses rs on r.ID = rs.RequestID
where r.DealerNumber = @DealerNumber
)
select @NotificationCount = Count(*) from Notifications n inner join Statuses s
on n.StatusId = s.ID
Where n.row_num = 1 and n.StatusId in (Select ID from Statuses where DealerPortal =
'Additional Info Needed')
and n.RequestStatusId not in (select RequestStatusId from AdditionalInfoViewed where
UserId = @UserId)
END
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387381.html
標籤：sql sql-server 公用表表达式
下一篇：從重復值中選擇第一個記錄名稱