# 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') <= 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. Mar 21 '20 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
Mar 22 '20 at 8:01
• I'm not reviewing the code, but there is nothing wrong with calling a function from a stored procedure. Mar 22 '20 at 11:24
• Stored procedures are usually the preferred way of doing things.
– Mast
Mar 24 '20 at 9:09
• Is there anything that makes you doubt about the approach?
– Mast
Mar 24 '20 at 9:09

Calling a function from a stored procedure is absolutely fine.

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

 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