在特定条件下更改计算列的值 [英] change the value of computed column in specific conditions

问题描述

我有一个图书馆流通表，其中有一个名为 Delay的列。此列是一个计算列，但应更改，直到 IsReturned（此表中的另一列，即位）等于0为止。这意味着延迟值应该更改并且更高，直到成员将书退还给图书馆。如何制作此计算列公式？
我尝试这样做，但这不是有效的公式：

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)

函数是这样的：

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

如您所知，当 IsReturned列等于1时， Delay列不应更改（我需要

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 ?

推荐答案

不允许引用计算列定义中的计算列。您需要记录该项目被检出并退回的时间，而不是使用BIT字段。您可以使用这些值来定义您的计算列。例如：

Referring to a computed column in a computed column definition is not allowed. You need to record the times that the item is checked out and returned rather than using a BIT field. You can use those values to define your computed column. For example:

CREATE TABLE Checkouts
(
    CheckoutId INT IDENTITY NOT NULL PRIMARY KEY, 
    CheckedOut DATETIME NOT NULL DEFAULT (getdate()), 
    CheckedIn DATETIME NULL, 
    DelayDays AS (datediff(day, CheckedOut, COALESCE(CheckedIn, getdate()))) 
) 

这篇关于在特定条件下更改计算列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！