Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello.
I have this table in my database :
SQL
CREATE TABLE [dbo].[Circulation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Document_Id] [int] NULL,
	[Person_Id] [int] NULL,
	[Librarian_Id] [int] NULL,
	[StartDate] [datetime] NULL,
	[EndDate]  AS ([StartDate]+[Time]),
	[Time] [int] NULL,
	[Delay]  AS ([dbo].[CalculateDelay]([Id],[IsReturned])),
	[IsReturned] [bit] NULL,
 CONSTRAINT [PK_Circulation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


and also this function :
SQL
ALTER FUNCTION [dbo].[CalculateDelay]
    (
      @Id INT ,
      @IsReturned BIT
    )
	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
                                     )
                        RETURN @delay
            END
			ELSE
			BEGIN
			RETURN NULL
			END
			RETURN NULL
    END


my problem is that if I want to alter the function, the following error display :

"Msg 3729, Level 16, State 3, Procedure CalculateDelay, Line 1
Cannot ALTER 'dbo.CalculateDelay' because it is being referenced by object 'Circulation'.
"

and if I want to remove the formula, this error display :

"'Circulation' table
- Unable to modify table.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
"

How can I alter the table or alter the function ?
Posted

Circulation is referring CalculateDelay field, thus you will not be able to alter this field.
 
Share this answer
 
Never rtied this but instead of fetching the time and startdate from the table, try sending them to the function as parameters. Something like:
SQL
ALTER FUNCTION [dbo].[CalculateDelay]
    (
      @IsReturned BIT,
      @StartDate DATETIME
      @Time INT      
    )
	RETURNS INT
AS
    BEGIN
        IF ( @IsReturned = 0 )
            BEGIN
                DECLARE @delay INT = ( SELECT   ( DATEDIFF(minute,
                                                           DATEADD(day, @Time,
                                                              @StartDate),
                                                           GETDATE()) )
                                     )
                        RETURN @delay
            END
			ELSE
			BEGIN
			RETURN NULL
			END
			RETURN NULL
    END


If that doesn't help, I'd suggest to move the calculation to a trigger.
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900