Nur Funktionen und einige erweiterte gespeicherte Prozeduren können innerhalb einer Funktion ausgeführt werden.

Question

Hallo,

ich bekomme von einem Kunden 2 Tabellen. In der einen sind Feiertage definiert und die Regionen in denen sie gültig sind.
Pro Region gibt es eine Spalte, deren Name aus einem Kürzel besteht.
In der zweiten Tabelle ist diesen Kürzeln die Regionsbezeichnung zugeordnet.

Die erste Tabelle möchte der Kunder erweiterbar haben, falls zusätzliche Regionen eingetragen werden sollen.
Ich habe nun ein Script gebaut, das eine 2-spaltige Liste der Feiertage mit den jeweils gültigen Regionen ausgibt.

Dieses Script habe ich als Funktion gespeichert. Wenn ich die Funktion benutze will, bekomme ich die folgende Fehlermeldung:

Nur Funktionen und einige erweiterte gespeicherte Prozeduren können innerhalb einer Funktion ausgeführt werden.

Das ist mein Script:

ALTER FUNCTION [dbo].[F_Feiertage_DE]()

RETURNS @Feiertage TABLE (
Feiertag			nvarchar(100),
Region			nvarchar(100)
)
AS
Begin
DECLARE @strSQL			nvarchar(2000)
DECLARE @FN			nvarchar(255)
DECLARE @ParmDefinition		nvarchar(500)
DECLARE @Feiertagsname		nvarchar(100)
DECLARE @FName		nvarchar(100)
DECLARE @Region			nvarchar(100)
DECLARE @Reg			nvarchar(100)

SET @strSQL = N'DECLARE Colnames CURSOR READ_ONLY FAST_FORWARD FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = ''Feiertage'' AND DATA_TYPE = ''bit'')'
EXEC sp_executesql @strSQL
		OPEN Colnames;
		FETCH NEXT FROM Colnames
		INTO @FN;
		WHILE @@FETCH_STATUS = 0
			BEGIN
			Set @FName = NULL
			Set @strSQL = N'SELECT @Feiertagsname = [Feiertagsname], @Region = [Regionsname] FROM Feiertage, Region WHERE ' + @FN + ' = 1 AND LKZ = ''D'' AND Regionskuerzel = ''' + @FN + ''''
			Set @ParmDefinition = N'@Feiertagsname nvarchar(100) OUTPUT, 
									@Region nvarchar(100) OUTPUT'
			EXEC sp_executesql @strSQL, @ParmDefinition, @Feiertagsname = @FName OUTPUT, @Region = @Reg OUTPUT
			IF @FName IS NOT NULL
				BEGIN
				INSERT INTO  @Feiertage (Feiertag, Region) VALUES (@FName, @Reg)
			END
			FETCH NEXT FROM Colnames
			INTO @FN;
			END
		CLOSE Colnames;
		DEALLOCATE Colnames;
		Return;
END

Liegt das Problem beim dynamischen SQL?
Wie kann ich das Problem umgehen?

Bin für jeden Tipp dankbar

Gruß

cheapy

Answer 1

a) verstehe ich nicht, wieso du dynamisches SQL verwendets, statt direktes SQL mit "select into."
b) wieso du überhaupt die Spaltennnamen abfragst wenn du die Tabelle doch kennst.

Statt also einer SP würde ich das einfach mit einer View lösen, die diese Spalten enthält.
Sollte es sich um mehrere ähnliche Tabellen handeln, kannst du auch eine Union-View über mehrere Tabellen erstellen.

Answer 2

Hallo,

zu a:
die erste Tabelle hat u.a. die Feldnamen A1, A2, ...
in der zweiten Tabelle steht im ersten Feld eben dieser Spaltenname aus der ersten Tabelle und in der zweiten Spalte die zugehörige Region.

zu b:
Der Kunde möchte bei Bedarf die erste Tabelle um zusätzliche Regionen erweitern können, dagher soll meine Abfrage variabel sein.

Wenn Du mir verraten kannst, wie ich Feldnamen der einen Tabelle mit den Feldinhalten der anderen Tabelle zusammen bringen kann, würde ich mich freuen. Vielleicht denke ich ja gerade viel zu kompliziert...

Hier die Felder von Tabelle 1

[Feiertagsname], [Art], [Monat], [Tag], [Osterdiff], [LKZ], [A0], [A1], [A2], [A3], [A4], [A5], [A6], [A7], [A8], [A9], [A10], [A11], [A12], [A13], [A14], [A15], [A16]

und hier die Felder von Tabelle 2:

[Regionskürzel], [Regionsbezeichnung]

Der Inhalt des Feldes Regionskürzel entspricht den Feldbezeichnungen A0 oder A1 oder ...
Die Ausgabe wäre dann z.B. die folgende Liste:

Feiertag   Regionsbezeichnung
Neujahr    Berlin
Neujahr    Bayern
...
...

Answer 3

Ich würde dir empfehlen, die Tabelle 1 zu normalisieren, also die Spalten A0 bis A16 zu einer Spalte mit mehreren Zeilen und 1 Spalte.
Dann kannst du diese Spalte per Join mit Tabelle 2 verknüpfen.
KLar ist das redundant aber übersichtlicher und einfacher zu pflegen.

Ansonsnten kannst du eben a)

mit Spalte A0 - A16 je einen left join auf die Tabelle 2 machen.
Oder einen b)

einen Union join der Keys und Spalten a0 - a16 mit Join auf Tabelle 2:

select * from (
select [Feiertagsname], [Art], [Monat], [Tag], [Osterdiff], [LKZ], [A0] from tabelle1 where [A0] <> ''
union all
select [Feiertagsname], [Art], [Monat], [Tag], [Osterdiff], [LKZ], [A1] from tabelle1 where [A1] <> ''
union all
:
:
:
select [Feiertagsname], [Art], [Monat], [Tag], [Osterdiff], [LKZ], [A16] from tabelle1 where [A16] <> ''

) a
inner join Tabelle2 on [a0] = [Regionskürzel]

Answer 4

Wie schon geschrieben, bekomme ich die Tabelle von einem Kunden, der sie in einer Applikation genau so nutzt.

Der Kern meiner Frage ist ja auch wie ich eine Funktion bauen kann, die mir das gewünschte Ergebnis bringt.

Ich möchte halt nicht für jedes hinzu kommende Regionsfeld eine weitere Union-Abfrgage bauen. Das ist der Hintergrund, weshalb ich das dynamische SQL verwendet habe.

Answer 5

Nun ja, da die Regionfelder A0 - A16 in der Tabelle fest stehen, muss bei einer Erweiterung ja sowieso die Tabelle und ggf. sogar die Anwendung erweitert werden.
Was spricht also dagegen, bei der Tabellenerweiterung den Union anzupassen?

Auch hier ist es eben häufiger sinnvoll mit statischen Tabellen/Views zu arbeiten.

Du kannst ja auch einen Union-Insert in eine temporary Table machen und daraus dein Ergebnis liefern.
https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/

Und noch eins:
"in der zweiten Tabelle steht im ersten Feld eben dieser Spaltenname aus der ersten Tabelle und in der zweiten Spalte die zugehörige Region. "

Hierzu habe ich keine Vorstellung, wie der Inhalt von [A0] auf die Spalte [Regionskürzel] verweist. Dann müsste doch überall des selbe stehen. Kannst du mal Beispieldaten zeigen?
Das ist SQL-technisch schon ein seltsames Konzept und klar, auch nur komplex umzusetzen.
Hier würde eher eine C#-Funktion zum Ziel führen. Da hast du mehr Freiheiten um sowas zu realisieren.

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-functions?view=sql-server-ver16

Answer 6

Hi,

poste bitte die Tabellenstrukturen als CREATE TABLE oder DECLARE ... TABLE Statements. Dazu einige Beispieldaten als INSERT INTO Statements und das gewünschte Ergebnis, basierend auf genau diesen Beispieldaten.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport