Table valued function not compiling

Question

I am trying to write an update and select statement in table valued function in sql server. I am currently getting a syntax error. Could somebody tell me what the problem is

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [declaration].[UserAgreementsOutstandingGet]
(
    @UserID INT
)
RETURNS TABLE 
AS
RETURN 
(

  -- Update the agreement as viewed 
   UPDATE declaration.[UserAgreement]
      SET AcceptanceWindowExpiry =  GETUTCDATE()
   WHERE USER_ID = @UserID;

    -- All outstanding user agreements that require attention.
    SELECT      ua.ID AS [UserAgreementID]
                ,a.ID AS [AgreementID]
                ,a.[Code]
                ,a.[ComplianceCode]
                ,a.[Name]
                ,a.[Description]
                ,a.[Version]
                ,ua.[UserAgreementStateID]
                ,uas.[Name]                             AS [UserAgreementStateName]
                ,ua.[ReviewPeriodInDays]
                ,a.[Data]
                ,pa.[ID]                                AS [AuthoredByID]
                ,pa.[FirstName] + ' ' + pa.[LastName]   AS [AuthoredByName]
                ,a.[Authored]
                ,ia.[ID]                                AS [IssuedByID]
                ,ia.[FirstName] + ' ' + pa.[LastName]   AS [IssuedByName]
                ,a.[Issued]
    FROM        declaration.[Agreement] a
    INNER JOIN  declaration.[UserAgreement] ua
        ON      a.[ID] = ua.[AgreementID]
    INNER JOIN  declaration.[UserAgreementState] uas
        ON      ua.[UserAgreementStateID] = uas.[ID]
    LEFT JOIN   common.[Person] pa
        ON      a.[AuthoredBy] = pa.[ID]
    LEFT JOIN   common.[Person] ia
        ON      a.[AuthoredBy] = ia.[ID]
    WHERE       ua.[UserID] = @UserID                                           
        AND     uas.[Code] IN ('ISS', 'DEF')                                        -- Issued, Deferred
        AND     a.[Draft] = CONVERT(BIT, 0)                                         -- Not a draft.
        AND     a.[Deleted] = CONVERT(BIT, 0)                                       -- Not deleted.
        AND     ISNULL(a.[Expires], '9999-12-31') > GETUTCDATE()                    -- Not expired (agreement level).
        AND     ISNULL(ua.[ReviewPeriodInDays], '9999-12-31') > GETUTCDATE()    -- User accepted window has not passed since their first notification.
)

Answer 1

You can do only read-only operations in functions.

You need to use a stored procedure if you want to perform DML operations.

Something like that:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE declaration.UserAgreementsOutstandingGet (@UserID INT)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Update the agreement as viewed 
        UPDATE declaration.UserAgreement
        SET AcceptanceWindowExpiry = GETUTCDATE()
        WHERE USER_ID = @UserID;

        -- All outstanding user agreements that require attention.
        SELECT ua.ID AS UserAgreementID
          , A.ID AS AgreementID
          , A.Code
          , A.ComplianceCode
          , A.Name
          , A.Description
          , A.Version
          , ua.UserAgreementStateID
          , uas.Name AS UserAgreementStateName
          , ua.ReviewPeriodInDays
          , A.Data
          , pa.ID AS AuthoredByID
          , pa.FirstName + ' ' + pa.LastName AS AuthoredByName
          , A.Authored
          , ia.ID AS IssuedByID
          , ia.FirstName + ' ' + pa.LastName AS IssuedByName
          , A.Issued
        FROM declaration.Agreement AS A
        INNER JOIN declaration.UserAgreement AS ua
            ON A.ID = ua.AgreementID
        INNER JOIN declaration.UserAgreementState AS uas
            ON ua.UserAgreementStateID = uas.ID
        LEFT JOIN common.Person AS pa
            ON A.AuthoredBy = pa.ID
        LEFT JOIN common.Person AS ia
            ON A.AuthoredBy = ia.ID
        WHERE ua.UserID = @UserID
        AND uas.Code IN ('ISS', 'DEF') -- Issued, Deferred
        AND A.Draft = CONVERT(BIT, 0) -- Not a draft.
        AND A.Deleted = CONVERT(BIT, 0) -- Not deleted.
        AND ISNULL(A.Expires, '9999-12-31') > GETUTCDATE() -- Not expired (agreement level).
        AND ISNULL(ua.ReviewPeriodInDays, '9999-12-31') > GETUTCDATE(); -- User accepted window has not passed since their first notification.
    END TRY
    BEGIN CATCH
        -- do some pseudo logging
        PRINT ERROR_MESSAGE();

        THROW;
    END CATCH;
END;