That SQL doesn't even compile.
Let's start with
Select * from CTE2 Order by userid
LEFT JOIN tblUsers u ON CTE2.userid = u.ID
LEFT JOIN tblService s ON CTE2.Id=s.Id
WHERE s.Id IS NULL
You can't put Order by before the JOIN definitions. Then you have some random code
BEGIN
IF NOT EXISTS (Select SA.ServiceID from @ServiceAuthorization SA WHERE SA.UserID=ID AND SA.ServiceID=Id)
BEGIN
PRINT 'RECORD EXISTS '
END
Else
BEGIN
INSERT into @ServiceAuthorization ([UserID], [ServiceID],[AddDate], [EditDate], [IsActive], [IsDisable], [CreatedBy]) VALUES (1, 1, CAST(N'2020-08-31T16:59:47.747' AS DateTime), CAST(N'2022-10-17T11:59:05.080' AS DateTime), 1, 0, 1)
END
END
which appears to have been copied from a stored procedure. You can't refer to the cte like that. Try inserting the output into a temporary file and use that for the Exists if you absolutely must - however ...
Do you really intend a cross-join in
cte
? That style of join is incredibly old-fashioned. One of the reasons it is not generally used is because of the danger of introducing cross-joins by forgetting to include appropriate
WHERE
clauses.
CTE2
seems a little pointless and I really advise you avoid using correlated sub-queries like that - they are very bad for performance.
Better yet, work out what you want to do and write the code to do it. Most of this looks like it has been copied from multiple locations (different naming conventions on tables and different join styles for example). It is vastly overcomplicated for such a simple task.
If you need to come back for further help, please explain clearly what you are trying to achieve and give us your table schemas and some sample data.