Hallo zusammen,
habe folgende Funktion im MS-SQL-Server2005 erstellt (hier als ALTER):
ALTER FUNCTION [dbo].[TAXNum2] (@DocNum as int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @TAXNum int
Declare @anz int
Declare @VISNum int
IF NOT EXISTS (Select DocNum from OTSI Where DocNum = @DocNum)
BEGIN
DECLARE tab_cursor CURSOR FOR
select
t1.VISNum
from
otsi t0 inner join
VAI_TAX_VIS t1 on t0.DocNum = t1.TAXNum
where
t0.Canceled = 'Y';
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @VISNum;
WHILE (@@FETCH_STATUS = 0)
Begin
IF NOT EXISTS (select t1.VISNum from otsi t0 inner join VAI_TAX_VIS t1 on t0.DocNum = t1.TAXNum where t0.Canceled = 'N' AND t1.VISNum = @VISNum)
BEGIN
SET @TAXNum = @VISNum
BREAK
END
ELSE
FETCH NEXT FROM tab_cursor INTO @VISNum;
End
CLOSE tab_cursor;
DEALLOCATE tab_cursor;
IF ISNULL(@TAXNum,0)=0
BEGIN
SET @TAXNum = (Select MAX(VISNum)+1 from VAI_TAX_VIS )
END
-- Write new values in VAI_TAX_VIS-Table
INSERT INTO VAI_TAX_VIS_TEST VALUES (@DocNum, @TAXNum);
-- set @taxnum = 666 -- to see that this line is accessed
END
ELSE
Begin
SET @TAXNum = -1
End
RETURN(@TAXNum)
END;
Aufgrund der INSERT-Zeile erhalte ich folgenden Fehler:
Msg 443, Level 16, State 15, Procedure TAXNum2, Line 41
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
Da ich jedoch innerhalb der Funktion diese Tabelle updaten muß, bin ich über jede Idee von Euch dankbar
Gruß
Kai
habe folgende Funktion im MS-SQL-Server2005 erstellt (hier als ALTER):
ALTER FUNCTION [dbo].[TAXNum2] (@DocNum as int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @TAXNum int
Declare @anz int
Declare @VISNum int
IF NOT EXISTS (Select DocNum from OTSI Where DocNum = @DocNum)
BEGIN
DECLARE tab_cursor CURSOR FOR
select
t1.VISNum
from
otsi t0 inner join
VAI_TAX_VIS t1 on t0.DocNum = t1.TAXNum
where
t0.Canceled = 'Y';
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @VISNum;
WHILE (@@FETCH_STATUS = 0)
Begin
IF NOT EXISTS (select t1.VISNum from otsi t0 inner join VAI_TAX_VIS t1 on t0.DocNum = t1.TAXNum where t0.Canceled = 'N' AND t1.VISNum = @VISNum)
BEGIN
SET @TAXNum = @VISNum
BREAK
END
ELSE
FETCH NEXT FROM tab_cursor INTO @VISNum;
End
CLOSE tab_cursor;
DEALLOCATE tab_cursor;
IF ISNULL(@TAXNum,0)=0
BEGIN
SET @TAXNum = (Select MAX(VISNum)+1 from VAI_TAX_VIS )
END
-- Write new values in VAI_TAX_VIS-Table
INSERT INTO VAI_TAX_VIS_TEST VALUES (@DocNum, @TAXNum);
-- set @taxnum = 666 -- to see that this line is accessed
END
ELSE
Begin
SET @TAXNum = -1
End
RETURN(@TAXNum)
END;
Aufgrund der INSERT-Zeile erhalte ich folgenden Fehler:
Msg 443, Level 16, State 15, Procedure TAXNum2, Line 41
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
Da ich jedoch innerhalb der Funktion diese Tabelle updaten muß, bin ich über jede Idee von Euch dankbar
Gruß
Kai
