title: Using CTE in SQL Function giving Error
body: <p>I am trying to create a stored procedure, but I'm getting an error:</p>
  
  <blockquote>
    <p>Select statements included within a function cannot return data to a client.</p>
    
    <p>Msg 156, Level 15, State 1, Procedure fnSc_Channels_GetChannelsUnreadPosts, Line 39<br>
    Incorrect syntax near the keyword 'SET'.</p>
  </blockquote>
  
  <p>Can anyone please help me out?</p>
  
  <pre><code>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 &gt; @LastReadTime
      )
      AND ActivityTypeID &lt;&gt; 6
      AND FromActorID &lt;&gt; @FrontEndActorID )
  
      RETURN @return_value
  END
  </code></pre>