Getting incorrect Syntax Error In Stored Procedure with CTE-CodePudding

I am trying to create a stored proc where I need to find all the latest entries of a given dealer number and then get the total count according to some conditions. I have created it like this:

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;

But I am getting compile time error

Incorrect Syntax near SET statement.

I am not sure where I am doing wrong. Please suggest.

CodePudding user response：

It's not allowed to use set variables in a CTE, you should SELECT variables as same rules as VIEW are in place or populate the variables outside the CTE

SELECT @UserId = ....

should work

Rules for variables in a common table expression: MSDN

CodePudding user response：

Set @UserId value before cte begins( before "with" keyword )

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