I have a table for Library Circulation that have a column named "Delay". This column is a computed column, but it should be changed until the "IsReturned" (another column in this table which is bit) is equal to 0. This means that the delay value should changed and be higher until the member return the book into the library. How can i make this computed column formula ? I try this but it is not a valid formula :
dbo.CalculateDelay(Id,IsReturned,Delay)
and the function was like that :
CREATE FUNCTION CalculateDelay
(
@Id INT ,
@IsReturned BIT ,
@LastDelay INT
)
RETURNS INT
AS
BEGIN
IF ( @IsReturned = 0 )
BEGIN
DECLARE @delay INT = ( SELECT ( DATEDIFF(minute,
DATEADD(day, [Time],
[StartDate]),
GETDATE()) )
FROM dbo.Circulation
WHERE Id = @Id
)
IF ( SQL_VARIANT_PROPERTY(@delay, 'BaseType') = 'int' )
BEGIN
RETURN @delay
END
ELSE
BEGIN
RETURN -5
END
END
RETURN @LastDelay
END
as you understand, The "Delay" column should not change when the "IsReturned" column is equal to 1 (I need to keep the delay in the database).
UPDATE :
I use this code for executing the function and it is working correctly :
DECLARE @g INT
EXEC @g = dbo.CalculateDelay 15 ,0 ,12000
SELECT @g
Is my code in the formula column incorrect ? or why it has error ?