CODEWITHSUNDEEP
sql-serverfunction
Dipendra Shekhawat
Dipendra Shekhawat

Reputation: 55

Using CTE in SQL Function giving Error

I am trying to create a stored procedure, but I'm getting an error:

Select statements included within a function cannot return data to a client.

Msg 156, Level 15, State 1, Procedure fnSc_Channels_GetChannelsUnreadPosts, Line 39
Incorrect syntax near the keyword 'SET'.

Can anyone please help me out?

ALTER FUNCTION [dbo].[fnSc_Channels_GetChannelsUnreadPosts]
(
    @FrontEndActorID INT
    , @ChannelActivityFeedActorID INT
)
RETURNS INT
AS
BEGIN
     DECLARE    @return_value AS INT

     --EXEC @return_value = [dbo].[iD_Channels_Channel_GetUnreadPostCount]
     --     @FrontEndActorID = 7,
     --     @ChannelActivityFeedActorID = 13

     -- RETURN @return_value

    DECLARE @LastReadTime DATETIME

    SELECT @LastReadTime = LastVisitTime
    FROM iDtbl_Channels_ChannelMemberLastVisitTime WITH(NOLOCK)
    WHERE
        ChannelActivityFeedActorID = @ChannelActivityFeedActorID 
        AND FrontEndActorID = @FrontEndActorID

    ;WITH ChannelFeedActors AS
    (
        SELECT A.ActivityFeedProfileID
        FROM iDtbl_Channels_Channel C WITH(NOLOCK) 
        INNER JOIN iDtbl_FileSystem_FrontEndActor A WITH(NOLOCK) ON C.OwnerFrontEndActorID = A.FrontEndActorID
        WHERE
            C.OwnerFrontEndActorID = @FrontEndActorID

        UNION ALL

        SELECT @ChannelActivityFeedActorID
)

SET @return_value = (SELECT ISNULL(COUNT(DISTINCT ActivityID), 0) UnreadPosts
FROM ChannelFeedActors A
INNER JOIN iDtbl_ActivityFeed_ActivityFeed AF WITH(NOLOCK) ON A.ActivityFeedProfileID = AF.ActivityFeedActorID
WHERE
    (
        @LastReadTime IS NULL
        OR DateAdded > @LastReadTime
    )
    AND ActivityTypeID <> 6
    AND FromActorID <> @FrontEndActorID )

    RETURN @return_value
END

Upvotes: 1

Views: 1481

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You have to use SELECT instead of SET:

ALTER FUNCTION [dbo].[fnSc_Channels_GetChannelsUnreadPosts]
(
    @FrontEndActorID INT
    , @ChannelActivityFeedActorID INT
)
RETURNS INT
AS
BEGIN

DECLARE @return_value AS INT

DECLARE @LastReadTime DATETIME

SELECT @LastReadTime = LastVisitTime
FROM iDtbl_Channels_ChannelMemberLastVisitTime WITH(NOLOCK)
WHERE
    ChannelActivityFeedActorID = @ChannelActivityFeedActorID 
    AND FrontEndActorID = @FrontEndActorID

;WITH ChannelFeedActors AS(
    SELECT A.ActivityFeedProfileID
    FROM iDtbl_Channels_Channel C WITH(NOLOCK) 
    INNER JOIN iDtbl_FileSystem_FrontEndActor A WITH(NOLOCK)
    ON C.OwnerFrontEndActorID = A.FrontEndActorID
    WHERE
        C.OwnerFrontEndActorID = @FrontEndActorID
    UNION ALL
    SELECT @ChannelActivityFeedActorID
)
-- Instead of using SET, use SELECT
-- Remove the column alias UnreadPosts
SELECT @return_value = ISNULL(COUNT(DISTINCT ActivityID), 0)
FROM ChannelFeedActors A
INNER JOIN iDtbl_ActivityFeed_ActivityFeed AF WITH(NOLOCK)  
ON A.ActivityFeedProfileID = AF.ActivityFeedActorID
WHERE
    (
        @LastReadTime IS NULL
        OR DateAdded > @LastReadTime
    )
    AND ActivityTypeID <> 6
    AND FromActorID <> @FrontEndActorID

RETURN @return_value

END

As for the error:

Msg 319, Level 15, State 1, Procedure fnSc_Channels_GetChannelsUnreadPosts, Line 41 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.

You have to terminate the previous statement before the CTE with a semicolon. 

DECLARE @var INT;

WITH CTE AS(...)

However, most people just begin their CTE declaration with a ;:

DECLARE @var INT

;WITH CTE AS(...)

Upvotes: 2

Related Questions