Calling a function within stored procedure to derive a value I am trying to write a sql storeprocedure that basically returns a derived value from an outcome of a function call. The  derived field returns the difference in days and if the difference is less than 24 hours then returns hours. It is working as desired but just need to ensure if the approach is right.

Storeprocedure query

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.AcceptanceWindowExpiry
          , declaration.GetDifferenceInDaysOrHours(ua.AcceptanceWindowExpiry) AS NoOfDays
          , 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.IssuedBy = ia.ID
        WHERE ua.UserID = 607
        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.Issued, '9999-12-31') &lt;= GETUTCDATE() -- Issued date less than equal to today's date (Issued date).   
        AND ISNULL(A.Expires, '9999-12-31') &gt; GETUTCDATE() 


I am not sure if writing a function and calling it within the storeprocedure has any implications and is the right way to do it. The results that I am getting are correct though. Could somebody throw some light.

CREATE FUNCTION declaration.GetDifferenceInDaysOrHours(@AcceptanceWindowExpiry datetime)  
RETURNS int   
AS   

BEGIN  
    DECLARE @timeDifferenceInDays INT;
    DECLARE @timeDifferenceInHours INT;
    DECLARE @timeDifference INT;

    SELECT @timeDifferenceInDays = DATEDIFF(d,  GETUTCDATE(), @AcceptanceWindowExpiry)  
    IF @timeDifferenceInDays &gt; 1
      BEGIN
            SELECT @timeDifference = @timeDifferenceInDays
       END
    ELSE 
     BEGIN
        SELECT @timeDifferenceInHours = DATEDIFF(HOUR,  GETUTCDATE(),  @AcceptanceWindowExpiry)  
          IF @timeDifferenceInHours &gt;= 0 AND @timeDifferenceInHours &lt;= 24
             BEGIN 
                SELECT @timeDifference = @timeDifferenceInHours
             END
          ELSE
            BEGIN
                SELECT @timeDifference = -1
          END
        END
        RETURN @timeDifference;  
    END;
Calling a function from a stored procedure is absolutely fine.

As far as the code goes, you could reduce the number of statements.

e.g. instead of

 IF @timeDifferenceInHours &gt;= 0 AND @timeDifferenceInHours &lt;= 24
     BEGIN 
        SELECT @timeDifference = @timeDifferenceInHours
     END
  ELSE
    BEGIN
        SELECT @timeDifference = -1
    END


I would suggest

  select @timedifference = 
    case when @timeDifferenceInHours &gt;= 0 AND @timeDifferenceInHours &lt;= 24
         then @timeDifferenceInHours
         else -1 
    end