Dynamisches SQL erlaubt die Definition und Ausführung von SQL-Anweisungen zur Laufzeit. Teile der Anweisungen befinden sich in Variablen, die ihre Werte zur Laufzeit ändern können. So kann ein dynamisch erstelltes SQL-Statement bei Veränderung der Variablen immer wieder verwendet werden ohne neu erstellt werden zu müssen.

Jeder von uns hat sicher bereits dynamisches SQL entwickelt und verwendet. Seltener wird dabei ein Ausgabewert benötigt. Aber genau um den soll es in diesem Blogbeitrag gehen, da die Programmierung eines Ausgabewertes innerhalb eines dynamischen SQL-Statements bestimmten Regeln unterliegt.

Im Beispiel wird eine Prozedur zum Kopieren von Werten der Wertart 1 zu Wertart 2 bei definiertem Zeitpunkt und Kunden in der Datenbank Chair erstellt. Es wird dafür dynamisches SQL eingesetzt, weil sich die zu kopierenden Werte in mehreren Tabellen befinden. Dabei soll zuvor ermittelt werden, ob zu diesem Zeitpunkt und bei diesem Kunden bei der Wertart 1, die kopiert werden soll, überhaupt Daten vorliegen. Diese Anzahl Datensätze ist also der Ausgabewert, der für jede Tabelle ermittelt wird.

Nun wird die Prozedur erstellt. Sie soll als Übergabeparameter den gewünschten Monat, den Kunden, die Wertart 1, welche kopiert werden soll, und die Wertart 2, in die kopiert werden soll, enthalten. Außerdem werden zu Beginn alle erforderlichen Variablen deklariert.

CREATE PROCEDURE [dbo].[ P_Kopieren_Wertart1_zu_Wertart2] 
@Period_ID varchar(10),
@ValueType1 varchar(50),
@ValueType2 varchar(50), 
@Customer varchar(10)

AS
BEGIN

DECLARE @Tabname varchar(100)
DECLARE @ValueTypeID1 varchar(1)
DECLARE @ValueTypeID2 varchar(1)
DECLARE @SQL varchar(500)
DECLARE @SQL_Anz nvarchar(500)
DECLARE @ParamDef nvarchar(500)
DECLARE @Anz int

Zunächst kann man in der Prozedur die Übergabeparameter auf Gültigkeit überprüfen. Das soll hier nicht weiter thematisiert werden und ist in dem vollständigen Skript der Prozedur enthalten, welches sich im Anhang dieses Blogs befindet.

Da das Kopieren für mehrere Tabellen durchgeführt werden soll, müssen diese zunächst ermittelt und eine Cursor-Variable deklariert werden:

DECLARE tab_cursor CURSOR FOR
SELECT distinct sysobjects.name 
FROM syscolumns 
inner join sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.type = 'U'
and syscolumns.name = 'Revenues'
and sysobjects.name like 'T_Import_%'
and sysobjects.name not like '%_Log'
and sysobjects.name not like '%_Rollback'

Es werden alle Tabellen, die eine Spalte “Revenues” enthalten, mit “T_Import_” beginnen und nicht auf “_Log” oder “_Rollback” enden, selektiert. In der Datenbank Chair sind dies die Tabellen T_IMPORT_FACT und T_Import_Deckungsbeitragsrechnung.

Nun soll ermittelt werden, ob diese Tabellen zu dem in den Parametern angegebenem Zeitpunkt und Kunden aktuelle Werte vorliegen.

Dafür öffnen wir den zuvor definierten Cursor und selektieren den Namen der ersten Tabelle in eine Variable @Tabname.

OPEN tab_cursor
FETCH NEXT FROM tab_cursor INTO @Tabname
WHILE @@fetch_status = 0
BEGIN

Um die Anzahl der Datensätze zu ermitteln, wird folgendes dynamisches SQL erstellt:

SET @SQL_Anz = N'select @AnzOut = COUNT(*) from ' + @Tabname
SET @SQL = @SQL + ' WHERE ValueType = ' + @ValueTypeID1 + ' AND Month = ' + @Period_ID
IF (@Customer_Flag = 1) SET @SQL = @SQL + ' AND Customer in (select CustomerID from T_S_Kunden_Stammdaten_Geo where DE_L4 = ''' + @Customer + ''')'

Die Variable @AnzOut soll die Anzahl der Datensätze enthalten. Wurde ein spezieller Kunde als Paramater an die Prozedur übergeben, wird der Kunde in die WHERE-Bedingung aufgenommen, sonst werden alle Kunden in die Abfrage einbezogen.

Mit der Anweisung:

SET @ParamDef = N'@AnzOut int OUTPUT'

wird die @AnzOut als Ausgabeparameter definiert.

Ausgeführt wird das SQL-Statement über:

EXECUTE sp_executesql @SQL_Anz, @ParamDef, @AnzOUT=@Anz OUTPUT

Ein einfaches Ausführen mit EXEC @SQL_Anz ist nicht möglich. Wichtig ist auch, dass sowohl die Variable für das SQL-Statement (@SQL_Anz) als auch die Variable für die Definition der Parameter (@ParamDef) vom Typ nvarchar sind.

Der eigentlich mit der Anzahl befüllte Parameter @AnzOut muss zudem in eine Variable (hier @Anz) kopiert werden, da es sich bei @AnzOut tatsächlich nur um einen Ausgabeparameter handelt, der nicht als Variable verwendet werden kann.

Mit der Variable @Anz kann nun die Entscheidung getroffen werden, ob Daten kopiert werden oder nicht.

IF (@Anz > 0)
BEGIN

-- Kopierprozess starten

END


 
Anhang
----------------------------------------------------------------------------- Prozedur zum Kopieren von Wertart1 -> Wertart2
-- erforderliche Parameter: Periode, Wertart, Customer
-- Periode: YYYYMM Bsp.: 201412
-- Customer: All für alle REUs
-- Customer für einen ausgewählte Kunden, Bsp.: Dobersohn 
-- Wertart: Ist, Plan,...
-- Mehrfachnennungen sind bei Periode und Customer nicht möglich. Die 
-- Prozedur kann nur für jeweils eine Angabe ausgeführt werden.
-- Prüfungen bei der Ausführung: Periode, Wertart1, Wertart2 und Kunde 
   (All, Customer)
---------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[P_Kopieren_Wertart1_zu_Wertart2] 
@Period_ID varchar(10),
@ValueType1 varchar(50),
@ValueType2 varchar(50),
@Customer varchar(10)

AS
BEGIN

DECLARE @ValueTypeID1 varchar(1)
DECLARE @ValueTypeID2 varchar(1)
DECLARE @Customer_Flag tinyint
DECLARE @Tabname varchar(100)
DECLARE @SQL varchar(500)
DECLARE @SQL_Anz nvarchar(500)
DECLARE @ParamDef nvarchar(500)
DECLARE @Anz int
DECLARE @message varchar(100)

----------------------------------------------------------------------------- Parameter @Customer überprüfen
---------------------------------------------------------------------------
SET @Customer_Flag = 10

IF (@Customer = 'All' or @Customer = '') SET @Customer_Flag = 0
ELSE
	BEGIN
IF ((SELECT COUNT(*) FROM T_S_Kunden_Stammdaten_Geo reg WHERE reg.DE_L4 = @Customer) > 0) SET @Customer_Flag = 1
	END
		
IF (@Customer_Flag = 10) 
BEGIN
	PRINT 'Kunde ist nicht gültig.'
	RETURN (-1)
END

----------------------------------------------------------------------------- Parameter @ValueType1 überprüfen
---------------------------------------------------------------------------SELECT @ValuetypeID1 = isnull(ValueTypeID,'') FROM T_IMPORT_Wertart we WHERE we.DE_Wertart = + @ValueType1

IF (LEN(@ValuetypeID1) = 0)
BEGIN
	PRINT 'Wertart1 ist nicht gültig.'
	RETURN(-1)
END

----------------------------------------------------------------------------- Parameter @ValueType2 überprüfen
---------------------------------------------------------------------------SELECT @ValuetypeID2 = isnull(ValueTypeID,'') FROM T_IMPORT_Wertart we WHERE we.DE_Wertart = + @ValueType2

IF (LEN(@ValuetypeID2) = 0)
BEGIN
	PRINT 'Wertart1 ist nicht gültig.'
	RETURN(-1)
END
	
----------------------------------------------------------------------------- Parameter @Periode überprüfen
---------------------------------------------------------------------------IF ((SELECT COUNT(*) FROM T_DIM_01_03_Monat WHERE MonatID = @Period_ID) = 0)
BEGIN
	PRINT 'Monat ist nicht gültig'
	RETURN (-1)
END

----------------------------------------------------------------------------- Cursor für Tabellen
---------------------------------------------------------------------------DECLARE tab_cursor CURSOR FOR
SELECT distinct sysobjects.name 
FROM syscolumns 
inner join sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.type = 'U'
and syscolumns.name = 'Revenues'
and sysobjects.name like 'T_Import_%'
and sysobjects.name not like '%_Log'
and sysobjects.name not like '%_Rollback'

OPEN tab_cursor
FETCH NEXT FROM tab_cursor INTO @Tabname
WHILE @@fetch_status = 0
BEGIN

	---------------------------------------------------------------------
-- Ermitteln, ob in der Tabelle Werte für Wertart1 für diese   
-- Periode und diese -- Kunden vorliegen
	---------------------------------------------------------------------
	SET @SQL_Anz = N'select @AnzOut = COUNT(*) from ' + @Tabname
SET @SQL_Anz = @SQL_Anz + ' WHERE ValueType = ''' + @ValueTypeID1 + ''' AND 		Month = ' + @Period_ID
IF (@Customer_Flag = 1) SET @SQL_Anz = @SQL_Anz + ' AND Customer in (select CustomerID from T_S_Kunden_Stammdaten_Geo
	where DE_L4 = ''' + @Customer + ''')'
		
	SET @ParamDef = N'@AnzOut int OUTPUT'
		
	EXECUTE sp_executesql @SQL_Anz, @ParamDef, @AnzOUT=@Anz OUTPUT
		
 
	---------------------------------------------------------------------
	-- Datensätze vorhanden
	---------------------------------------------------------------------	IF (@Anz > 0)
	BEGIN
	
		---------------------------------------------------------------
		-- Kopierprozess starten
		---------------------------------------------------------------
		BEGIN TRY
		BEGIN TRANSACTION
					
		---------------------------------------------------------------
		-- vorhandene Datensätze mit Wertart = @ValueType2 löschen
		---------------------------------------------------------------
SET @SQL = 'DELETE FROM ' + @Tabname + ' WHERE ValueType = ''' + @ValueTypeID2 + ''' AND Month = ' + @Period_ID 
IF (@Customer_Flag = 1) SET @SQL = @SQL + ' AND Customer in (select CustomerID from T_S_Kunden_Stammdaten_Geo
		where DE_L4 = ''' + @Customer + ''')'
			
		EXEC (@SQL)
			
		---------------------------------------------------------------
-- Datensätze mit Wertart = @ValueType1 in Hilfstabelle 
-- schreiben
		---------------------------------------------------------------
		SET @SQL = 'SELECT * INTO ' + @Tabname + '_tmp'  
SET @SQL = @SQL + ' FROM ' + @Tabname + ' where ValueType = ''' + @ValueTypeID1 + ''' AND Month = ' + @Period_ID  
IF (@Customer_Flag = 1) SET @SQL = @SQL + ' AND Customer in (select CustomerID from T_S_Kunden_Stammdaten_Geo
		where DE_L4 = ''' + @Customer + ''')'
			
		EXEC (@SQL)
			
		---------------------------------------------------------------
		-- ValueType, ChangeDate und User aktualisieren
		---------------------------------------------------------------
		SET @SQL = 'UPDATE ' + @Tabname + '_tmp'
		SET @SQL = @SQL + ' SET ValueType = ''' + @ValueTypeID2 + ''''
		
		EXEC (@SQL)
			
		---------------------------------------------------------------
		-- Datensätze aus Hilfstabelle einfügen
		---------------------------------------------------------------
		SET @SQL = 'INSERT INTO ' + @Tabname
		SET @SQL = @SQL + ' SELECT * FROM ' + @Tabname + '_tmp'
			
		EXEC (@SQL)
			
		---------------------------------------------------------------
		-- Hilfstabelle löschen
		---------------------------------------------------------------
		SET @SQL = 'DROP TABLE ' + @Tabname + '_tmp' 
		
		EXEC (@SQL)
			
		COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
			-- Transaction zurücksetzen
			ROLLBACK TRANSACTION
			
SET @message = 'Kopieren von ' + @ValueType1 + ' zu ' + @ValueType2 + ' ist fehlgeschlagen.' 
					+ char(13)
					+ error_message()
			PRINT @message
		END CATCH		
	END
		
	FETCH NEXT FROM tab_cursor INTO @Tabname	
END
CLOSE tab_cursor
DEALLOCATE tab_cursor

END