Calling a function within stored procedure to derive a value

Question

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') <= GETUTCDATE() -- Issued date less than equal to today's date (Issued date).   
        AND ISNULL(A.Expires, '9999-12-31') > 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 > 1
      BEGIN
            SELECT @timeDifference = @timeDifferenceInDays
       END
    ELSE 
     BEGIN
        SELECT @timeDifferenceInHours = DATEDIFF(HOUR,  GETUTCDATE(),  @AcceptanceWindowExpiry)  
          IF @timeDifferenceInHours >= 0 AND @timeDifferenceInHours <= 24
             BEGIN 
                SELECT @timeDifference = @timeDifferenceInHours
             END
          ELSE
            BEGIN
                SELECT @timeDifference = -1
          END
        END
        RETURN @timeDifference;  
    END;

This statement How do I call this function from within the query. makes the question off-topic since the code isn't working. — pacmaninbw
– pacmaninbw ♦, Commented Mar 21, 2020 at 23:10
I have re framed the question. Update the post. Just to let you code is working. I am looking at the approach ? — Tom
– Tom, Commented Mar 22, 2020 at 8:01
I'm not reviewing the code, but there is nothing wrong with calling a function from a stored procedure. — pacmaninbw
– pacmaninbw ♦, Commented Mar 22, 2020 at 11:24
Stored procedures are usually the preferred way of doing things. — Mast
– Mast ♦, Commented Mar 24, 2020 at 9:09

George Barwood · Accepted Answer · 2020-04-02 20:44:31Z

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 >= 0 AND @timeDifferenceInHours <= 24
     BEGIN 
        SELECT @timeDifference = @timeDifferenceInHours
     END
  ELSE
    BEGIN
        SELECT @timeDifference = -1
    END

I would suggest

  select @timedifference = 
    case when @timeDifferenceInHours >= 0 AND @timeDifferenceInHours <= 24
         then @timeDifferenceInHours
         else -1 
    end

Stack Exchange Network

Calling a function within stored procedure to derive a value

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Calling a function within stored procedure to derive a value

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions