Ungültige Verwendung des Operators mit Nebenwirkungen

Question

Hallo,

ich habe eine einfache UDF erstellt, welche mir eine Tabelle mit Monaten zwischen einem Start und Enddatum wiedergeben soll.

Beim Ausführen wird vom SQL-Server aber die Fehlermeldung:

Ungültige Verwendung des Operators mit Nebenwirkungen oder Zeitabhängigkeit in 'INSERT' innerhalb einer Funktion.

gesetzt. 

Woran könnte das liegen?

--------------------------------------------------------------------------------------------

-- Die Funktion gibt eine Tabelle der zwischen Start und End-Datum liegende Monate zurück
-- (1 Monat = 1 DS)

CREATE
FUNCTION ar.TabMonate(@StartDATE datetime = getdate, @EndDATE datetime = getdate)

RETURNS

 @tabMonate TABLE (ID int primary key NOT NULL, Monat int NOT NULL, Jahr int NOT NULL)

AS

BEGIN

DECLARE @ID INT -- Tabellen ID
DECLARE @EMonat INT -- Ende Monat
DECLARE @EJahr INT -- Ende Jahr
DECLARE @AMonat INT -- Aktueller Monat
DECLARE @AJahr INT -- Aktuelles Jahr

SET @ID = 1
SET @AMonat = DATEPART(mm,@StartDATE)
SET @AJahr = DATEPART(yy,@StartDATE)
SET @EMonat = DATEPART(mm,@EndDATE)
SET @EJahr = DATEPART(yy,@EndDATE)

WHILE (@AJahr * 100 + @AMonat <= @EJahr * 100 + @EMonat) -- Alle Monate durchgehen bis Ende Jahr und Monat erreicht ist
BEGIN
   INSERT INTO tabMonate (ID, Monat, Jahr) VALUES (@ID, @AMonat, @AJahr); -- Aktuelle Werte in der Ergebnis-Tabelle speichern
   SET @AMonat = @AMonat +1; -- Aktuellen Monat inkrementieren 
   if (@AMonat > 12) BEGIN SET @AMonat = 1; SET @AJahr = @AJahr+1; END -- Bei Jahresberschreitung: Monat zurcksetzen und Jahr inkrementieren   
   SET @ID = @ID + 1; -- ID inkrementieren
END
RETURN;
END;

Answer 1

Hallo!
Beim INSERT INTO fehlt ein @ vor dem Namen der Tabellenvariablen.

Hier mal ein ganz anderer Ansatz, um solche Tabellenfunktionen mit verschiedensten Anforderungen zu realisieren! Die ursprüngliche Idee kommt von Steve Kass.

create function udf_Numbers
(
 @from as bigint,
 @to as bigint
)
returns table with schemabinding
as
return
     with
     t0(n) as
             (
             select 1
                     union all                                                -- Erzeugt zwei Sätze = 2^0
             select 1
             ),
     t1(n) as
             (
             select 1                                                        -- Cross Join erzeugt 4 Sätze = 
2^2
             from        t0 as a,
                             t0 as b
             ),
     t2(n) as
             (
             select 1                                                        -- Cross Join erzeugt 16 Sätze = 
2^4
             from        t1 as a,
                             t1 as b
             ),
     t3(n) as
             (
             select 1                                                        -- Cross Join erzeugt 256 Sätze 
= 2^8
             from        t2 as a,
                             t2 as b
             ),
     t4(n) as
             (
             select 1
             from t3 as a, t3 as b                                -- Cross Join erzeugt 65.536 
Sätze = 2^16
             ),
     t5(n) as
             (
             select 1 from t4 as a, t4 as b                -- Cross Join erzeugt 
4.294.967.296 Sätze = 2^32
             ),
     Numbers(n) as                                                        -- nummeriert die Sätze durch 
beginnend bei 1
             (
             select row_number() over (order by n) as n
             from t5
             )
     select @from + n - 1 as n                        -- addiert die laufende Nummer auf 
den Startwert
     from Numbers
     where n <= @to - @from + 1                        -- so lange die Anzahl der Zeilen 
eingehalten wird
go
 Select *
from udf_Numbers(1, 10000);

--
-- Erzeuge mir die Daten für die Monate ab Startdatum heute
--
Declare @StartDatum datetime = getdate(),
             @EndeDatum datetime ;

Select @EndeDatum = dateadd(M, 14, @StartDatum);

select dateadd(M,n,convert(char(8),DATEADD(MONTH, DATEDIFF(MONTH, 0,
@StartDatum), 0),112)) as Kalender
from dbo.udf_Numbers(1, datediff(MONTH,@StartDatum,@EndeDatum)) ;
 --
-- Droppe die Funktion wieder
-- Ignoriere den Fehler, falls Funktion nicht vorhanden
--
Begin Try DROP FUNCTION udf_Numbers end Try Begin Catch end Catch;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 2

Weil DATEPART() nicht deterministisch ist. Nimm MONTH() und YEAR() und dann sollte es gehen.

Answer 3

Hallo Christoph!

Da muss ich mich wohl schämen! Ein Syntax-Fehler!! Da bin ich von den jetzigen IDE's zu sehr verwöhnt... (wenigstens farblich hätte...)

Deine Beispiel-Funktion ist so was von flexibel. Ist ja ein richtiger Datensatz-Generator.

Vielen Dank!

Fred.

Answer 4

Hallo Stefan!

war ein Syntaxfehler meinerseits. Das es mit deterministischen Funktionen nicht funktioniert hatte ich auch gelesen, aber niergens eine Auflistung gefunden welche Funktionen deterministisch sind.

Trotzdem Danke für deine Bemühung(en).

Fred.