SQL Server 2014 with clause inside function/procedure

Question

Is it possible to make a user defined function /user defined procedure with the "with" clause inside it?

CREATE FUNCTION udf_UsersComments (
    @Id INT
    )
RETURNS @UsersComments TABLE (
    CommentTextFormatted NVARCHAR(MAX),
    DateCommented NVARCHAR(MAX),
    Username NVARCHAR(255),
    ParentCommentId INT,
    Id INT
    )
AS
BEGIN
WITH UpperHierarchy (Id, QuestionId, CommentText, ParentCommentId, DateCommented, UserId, HierarchyOrder, 
     lineage) 
     AS (SELECT com.Id,
                com.QuestionId, 
                com.CommentText, 
                com.ParentCommentId,
                com.DateCommented,
                com.UserId,
                0                          AS HierarchyOrder, 
                Cast ('/' AS VARCHAR(255)) AS Lineage 
         FROM   Comments AS com 
         WHERE  com.ParentCommentId IS NULL AND IsDeleted=0
         UNION ALL
         (SELECT com.Id,
                com.QuestionId,
                com.CommentText, 
                com.ParentCommentId,
                com.DateCommented,
                com.UserId,
                HierarchyOrder + 1, 
                Cast(lineage + Ltrim(Str(com.ParentCommentId, 6, 0)) 
                     + '/' AS VARCHAR(255)) 
         FROM   Comments AS com 
                INNER JOIN UpperHierarchy AS parent 
                        ON com.ParentCommentId = parent.Id
                        WHERE com.IsDeleted=0))

SELECT CommentTextFormatted, DateCommented, U.Username, ParentCommentId, Com.id
FROM Questions AS Q
INNER JOIN 
    (SELECT Space(HierarchyOrder*5) + CommentText AS CommentTextFormatted, Id, QuestionId, ParentCommentId, DateCommented, UserId, lineage
    FROM   UpperHierarchy) AS Com
ON Com.QuestionId=Q.Id
INNER JOIN Users AS U
ON U.Id=Com.UserId
WHERE Q.Id=@Id
ORDER  BY lineage + Ltrim(Str(Q.Id, 6, 0))
RETURN
END
GO

And I am getting this error

Msg 444, Level 16, State 2, Procedure udf_UsersComments, Line 13 Select statements included within a function cannot return data to a client.

I'm voting to close this question as off-topic because OP is so lazy to test it by it self. Is a better idea to try and, if don't work as expected, ask about behavior. — dani herrera, Dec 24 '17 at 16:05
Yes. If you're having a problem doing so, add the code you've tried to your question. — Dan Guzman, Dec 24 '17 at 16:06

Pரதீப் · Accepted Answer · 2017-12-24T16:37:21.597

Make it as a Inline table valued function. Check this question to know why I chose inline instead of multi line table valued function

CREATE FUNCTION udf_UsersComments (
    @Id INT
    )
RETURNS TABLE 
AS
Return(
WITH UpperHierarchy (Id, QuestionId, CommentText, ParentCommentId, DateCommented, UserId, HierarchyOrder, 
     lineage) 
     AS (SELECT com.Id,
                com.QuestionId, 
                com.CommentText, 
                com.ParentCommentId,
                com.DateCommented,
                com.UserId,
                0                          AS HierarchyOrder, 
                Cast ('/' AS VARCHAR(255)) AS Lineage 
         FROM   Comments AS com 
         WHERE  com.ParentCommentId IS NULL AND IsDeleted=0
         UNION ALL
         (SELECT com.Id,
                com.QuestionId,
                com.CommentText, 
                com.ParentCommentId,
                com.DateCommented,
                com.UserId,
                HierarchyOrder + 1, 
                Cast(lineage + Ltrim(Str(com.ParentCommentId, 6, 0)) 
                     + '/' AS VARCHAR(255)) 
         FROM   Comments AS com 
                INNER JOIN UpperHierarchy AS parent 
                        ON com.ParentCommentId = parent.Id
                        WHERE com.IsDeleted=0))

SELECT CommentTextFormatted, DateCommented, U.Username, ParentCommentId, Com.id,ordercol = lineage + Ltrim(Str(Q.Id, 6, 0))
FROM Questions AS Q
INNER JOIN 
    (SELECT Space(HierarchyOrder*5) + CommentText AS CommentTextFormatted, Id, QuestionId, ParentCommentId, DateCommented, UserId, lineage
    FROM   UpperHierarchy) AS Com
ON Com.QuestionId=Q.Id
INNER JOIN Users AS U
ON U.Id=Com.UserId
WHERE Q.Id=@Id)

Note, I have added another column in result to do the ordering while selecting the function. You cannot use Order by without TOP inside a function

select CommentTextFormatted, DateCommented, Username, ParentCommentId, id
from udf_UsersComments(1)--some id
order by ordercol

Regarding your original issue, you are missing insert into @UsersComments. CTE select should insert the records into @UsersComments

still getting errors Msg 156, Level 15, State 1, Procedure udf_UsersComments, Line 8 Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Procedure udf_UsersComments, Line 8 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 102, Level 15, State 1, Procedure udf_UsersComments, Line 43 Incorrect syntax near ')'. Msg 102, Level 15, State 31, Procedure udf_UsersComments, Incorrect syntax near 'BEGIN'. — john, Dec 24 '17 at 16:16
I mean now I lose the order of it + I get /1 and the root in order col — john, Dec 24 '17 at 16:34
@john As I have mentioned already in my answer, do the ordering while selecting the function, it is not possible to use order by inside a function. Check the update. — Pரதீப், Dec 24 '17 at 16:38

SQL Server 2014 with clause inside function/procedure

1 Answers1