1
\$\begingroup\$

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;
\$\endgroup\$
6
  • \$\begingroup\$ This statement How do I call this function from within the query. makes the question off-topic since the code isn't working. \$\endgroup\$
    – pacmaninbw
    Mar 21 '20 at 23:10
  • \$\begingroup\$ I have re framed the question. Update the post. Just to let you code is working. I am looking at the approach ? \$\endgroup\$
    – Tom
    Mar 22 '20 at 8:01
  • 1
    \$\begingroup\$ I'm not reviewing the code, but there is nothing wrong with calling a function from a stored procedure. \$\endgroup\$
    – pacmaninbw
    Mar 22 '20 at 11:24
  • \$\begingroup\$ Stored procedures are usually the preferred way of doing things. \$\endgroup\$
    – Mast
    Mar 24 '20 at 9:09
  • \$\begingroup\$ Is there anything that makes you doubt about the approach? \$\endgroup\$
    – Mast
    Mar 24 '20 at 9:09
2
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.