Original von Erland Sommarskog; deutsche Übersetzung von Frank Kalis

Wenn Sie die verschiedenen Newsgroups über Microsoft SQL Server verfolgen, wird Ihnen auffallen, dass häufig Fragen gestellt werden, warum die Statements:

   SELECT * FROM @tablename
   SELECT @colname FROM tbl
   SELECT * FROM tbl WHERE x IN (@list)

nicht funktionieren.

Oft antwortet dann jemand: "Benutz dynamisches SQL." und zeigt sogleich anhand eines einfachen Beispiel, wie dies funktioniert. Was aber vielfach dabei vergessen wird, ist den Leuten auch etwas über die Nachteile von dynamischem SQL zu sagen.

In diesem Artikel werde ich die Verwendung von dynamischem SQL in gespeicherten Prozeduren im Microsoft SQL Server diskutieren, und ich möchte zeigen, dass dies ein mächtiges Feature ist, welches mit Vorsicht benutzt werden sollte. Zunächst allerdings möchte ich beschreiben, warum wir überhaupt gespeicherte Prozeduren verwenden, bevor ich dynamisches SQL als solches behandle. Es folgt eine Betrachtung über den Konflikt zwischen den Vorteilen gespeicherter Prozeduren und den Auswirkungen des dynamischen SQL. Anschliessend gehe ich auf das als SQL Injektion bekannte Sicherheitsrisiko ein und stelle diverse gute Programmierungspraktiken dar. Den Abschluss bildet die Betrachtung einer Reihe von Fallbeispielen, in denen dynamisches SQL oft als Lösung genannt wird. Beispiel, bei denen dynamisches SQL tatsächlich die bessere Lösung ist, aber auch Beispiele, wo es die schlechtere Lösung ist. Für diesen Fall werde ich alternative Strategien vorschlagen.

 

Inhalt:

Warum gespeicherte Prozeduren?
EXEC() und sp_executesql
EXEC()
sp_executesql
Die Qual der Wahl
Cursors und dynamisches SQL
Dynamisches SQL und gespeicherte Prozeduren
SQL Injektion - ein ernstes Sicherheitsproblem
Gute Kodierpraktiken und dynamisches SQL
Beliebte Beispiele, dynamisches SQL (nicht) zu benutzen
select * from @tablename
select * from sales + @yymm
update tbl set @colname = @value where keycol = @keyval
select * from @dbname + '..tbl'
select * from tbl where col in (@list)
select * from tbl where @condition
Dynamische Suchbedingungen
select * from table order by @col
select top @n from table order by @col
create table @tbl
Linked Servers
OPENQUERY
Dynamische Spaltenbreite
Danksagung und Feedback
Revisionsgeschichte

Warum gespeicherte Prozeduren?

Bevor wir uns nun tatsächlich anschauen, was dynamisches SQL eigentlich ist, müssen wir kurz betrachten, warum wir überhaupt gespeicherte Prozeduren verwenden. Sie können eine komplexe Applikation entwickeln, in der Sie reine SQL Statements vom Client oder einer Zwischenschicht senden, ohne irgendwelche gespeicherte Prozeduren zu verwenden. Allerdings verwenden wir diese nicht deshalb, weil sie nun gerade hip sind, sondern weil sie verschiedene Vorteile bringen.

1. Das Berechtigungssystem 
Gespeicherte Prozeduren sind der klassische Weg, um Benutzern kontrollierten Zugang zu den Daten zu gewähren. Benutzer sollten nicht die Berechtigungen haben, SELECT, INSERT, UPDATE oder DELETE Statements direkt durchzuführen, da sie Missbrauch damit betreiben können, falls sie über ein Tool wie den Query Analyzer verfügen. Zum Beispiel könnten Sie in der Personaldatenbank ganz einfach ihr Gehalt erhöhen... Setzt man hingegen gespeicherte Prozeduren ein, benötigen Benutzer nicht mehr direkte Berechtigungen auf Tabellenebene, da bei Ausführung die Rechte des Eigentümers der Prozedur zur Anwendung kommen.

Heutzutage existieren noch ein paar zusätzliche Optionen. Bevor man Benutzern direkten Zugriff gewährt, können Sie zum Beispiel Zugriff auf Anwendungsrollen gewähren, die ein Passwort erfordern, um aktiviert zu werden. Dieses Passwort können Sie in der Anwendung selber verstecken. Anwendungsrollen wurden mit SQL Server Version 7.0 eingeführt. Noch sicherer ist die Verwendung einer Zwischenschicht wie COM+, da in diesem Fall die Benutzer nicht einmal direkten Zugang zum SQL Server zu haben brauchen.

Falls Sie aber weder Anwendungsrollen noch COM+ verwenden, ist die Verwendung gespeicherter Prozeduren so wichtig wie eh und je für die Sicherheit in SQL Server.

2. Zwischenspeicherung von Ausführungsplänen 
Ein weiterer wichtiger Grund für die Verwendung von gespeicherten Prozeduren ist die Verbesserung der Performance. Wird eine gespeicherte Prozedur erstmalig ausgeführt, erstellt SQL Server einen Ausführungsplan und platziert diesen im Cache, so dass bei der nächsten Ausführung SQL Server diesen Ausführungsplan wiederverwenden kann. Der Plan bleibt solange im Cache, bis er veraltet ist oder SQL Server einen Grund findet, einen neuen Plan erstellen zu müssen (Tatsächlich kann dies während der Ausführung der Prozedur passieren, nähere Erläuterungen hierzu sprengen aber den Rahmen dieses Artikels).

SQL Server cached auch Abfragepläne für SQL Statements, die ausserhalb von gespeicherten Prozeduren ausgeführt werden. Es können sogar Abfragen autoparametrisiert werden, so zum Beispiel die Statements

    SELECT * FROM pubs..authors WHERE state = 'CA'

    go

    SELECT * FROM pubs..authors WHERE state = 'WI'

Das zweite SELECT Statement benutzt den Abfrageplan der ersten Abfrage, da SQL Server die Abfrage gecached hat als

   SELECT * FROM pubs..authors WHERE state = @1

Wenn aber das SQL Statement komplexer ist, ist SQL Server nicht immer in der Lage, zu autoparametrisieren. Mir ist aufgefallen, dass SQL Server teilweise ein Statement im Cache nicht findet, wenn der einzige Unterschied in den Leerstellen liegt. Festzuhalten bleibt, dass SQL Server gute Arbeit beim Cachen von reinen SQL Statements leistet, die Verwendung von gespeicherten Prozeduren aber immer noch die höhere Wahrscheinlichkeit der Wiederverwendung von Abfrageplänen bietet.

Caching wird besonders wichtig, wenn kleine und kurze gespeicherte Prozeduren (oder SQL Statements) häufig ausgeführt werden. In diesem Fall können 500ms, die zur Erstellung des Abfrageplanes benötigt werden, durchaus Overhead darstellen. Hingegen sind die 3 Sekunden, die zur Generierung des Abfrageplanes einer Prozedur benötigt werden, welche dann 20 Minuten läuft, kein wirklicher Grund zur Sorge.

Sollten Sie noch SQL Server 6.5 verwenden, beachten Sie bitte, dass diese Version keine reinen SQL Statements cached, sondern nur Abfragepläne für gespeicherte Prozeduren.

3. Netzwerkverkehr minimieren 
Dieser Punkt ist ebenfalls ein Performanceaspekt. Angenommen, Sie haben ein komplexes SQL Statement, dass sich über 50 Zeilen erstreckt, bei dem sich aber von Ausführung zu Ausführung nur einige Werte in der WHERE Bedingung ändern. Packen Sie nun dieses Statement in eine gespeicherte Prozedur, reduziert sich deutlich die Anzahl der Bytes, die über das Netzwerk gesendet werden müssen und wenn der Netzwerkverkehr generell hoch ist, kann diese gespeicherte Prozedur durchaus einen Performanceschub geben.

Dies kommt dann umso mehr zum Tragen, wenn Ihre Programmlogik aus mehreren SELECT/INSERT/UPDATE Schritten besteht, die voneinander abhängig sind. Verwenden Sie die gespeicherte Prozeduren Variante so können Sie temporäre Tabellen oder Variablen verwenden, um die Daten auf dem Server zu halten. Sollten Sie hingegen reine SQL Statements verwenden, müssen Sie Daten zwischen dem Server und dem Client oder einer Zwischenschicht hin und her bewegen (Strenggenommen ist dies nicht ganz richtig. Durch reine SQL Statements können Sie natürlich auch temporäre Tabellen erstellen und dort die Daten halten. Aber dann müssen Sie auf Connection Pooling und Disconnected Recordsets achten).

4. Output Parameter verwenden 
Wenn Sie ein SQL Statement ausführen wollen, dass eine einzige Zeile zurückgibt, so geschieht dies als Ergebnismenge, wenn Sie reine SQL Statements verwenden. Durch gespeicherte Prozeduren haben Sie die Alternative Ihre Ergebnisse in Form von Output Parametern zu erhalten, was deutlich schneller ist. Für eine einzige Abfrage in der Unterschied in absoluten Grössen gemessen vernachlässigbar. Wenn Sie aber 2.000 Aufrufe an den SQL Server schicken, zum Beispiel um folgendes zu machen:

    INSERT tbl (...) VALUES (...)

    SET @key = @@identity

werden Sie wahrscheinlich einen deutlichen Unterschied bemerken, wenn Sie @key als Output Parameter zurückgeben.

5. Logik kapseln 
Dies ist keine Frage der Sicherheit oder Performance, sondern vielmehr eine Frage der Modularisierung Ihres Codes. Durch den Einsatz gespeicherter Prozeduren müssen Sie Ihren Client Code nicht durch die Konstruktion von SQL Statements belasten. Nun steht nirgendwo geschrieben, dass Sie gespeicherte Prozeduren verwenden müssen. Genauso gut können Sie Prozeduren in Ihrem Client Code schreiben, welche die SQL Statements anhand der übergebenen Parameter zusammensetzen (Vielleicht stellen Sie dabei fest, dass Ihr SQL Code innerhalb der Syntax der Hostsprache untergeht).

Ein Spezialfall existiert allerdings: Sie verwenden keine andere Applikation als den Query Analyzer. Sie schreiben gespeicherte Prozeduren, die von Administratoren verwendet werden sollen. Hier sind diese der einzige verfügbare Container für die Programmlogik.

6. Nachhalten, was verwendet wird 
In einem komplexen System mit hunderten von Tabellen, werden Sie oft wissen wollen, wo eine bestimmte Tabelle oder Spalte referenziert wird. Zum Beispiel dann, wenn Sie vorhaben, diese Spalte zu ändern. Haben Sie Ihren Code ausschliesslich in gespeicherten Prozeduren, brauchen Sie nur diese zu durchsuchen, um die entsprechenden Stellen zu finden. Oder Sie erstellen einfach die Datenbank ohne die betreffende Spalte oder Tabelle und warten ab, was passiert. Darüber hinaus gibt es auch die Systemtabelle sysdepends und die gespeicherte Systemprozedur sp_depends. Allerdings ist es schwierig, die Informationen in sysdepends stets auf dem aktuellen Stand zu halten.

Lassen Sie hingegen zu, dass reine SQL Statement von der Applikation aus gesendet werden, haben Sie ein noch viel grösseres Problem. Sie müssen eine deutlich grössere Menge von Quellcode durchsuchen und wenn Ihre Tabellenspalte einen Allerweltsnamen wie status hat, sind Sie so gut wie verloren. Und sysdepends wird komplett irrelevant.

EXEC() und sp_executesql

SQL Server bietet zwei Wege an, dynamisches SQL auszuführen, EXEC() und sp_executesql

EXEC()
EXEC() ist die einfachere Variante, wie dieses Beispiel zeigt:

   SELECT @table = 'sales' + @year + @month

   EXEC('SELECT * FROM ' + @table)

Nun, obwohl dies wunderbar einfach aussieht, lassen sich doch einige wichtige Dinge festhalten. Das erste ist, das es stets die Berechtigungen des aktuellen Benutzers sind, die angewendet werden, auch wenn das Statement innerhalb einer gespeicherten Prozedur ausgeführt wird. Das zweite ist, dass EXEC() sehr ähnlich dem EXEC einer gespeicherten Prozedur ist. Statt nun aber eine solche auszuführen, führen Sie tatsächlich einen Batch einzelner SQL Statements aus. Aber, als würden Sie eine weitere gespeicherte Prozedur ausführen, erstellt der Batch einen eigenen Gültigkeitsbereich und verwendet nicht den der aufrufenden Prozedur. Dies bringt eine Reihe von Implikationen mit sich:

• Innerhalb des Batches können Sie nicht auf lokale Variablen oder Parameter der aufrufenden Prozedur zugreifen.
• Jegliches USE Statement hat keine Auswirkung auf die aufrufende Prozedur.
• Temporäre Tabellen, die innerhalb des Batches erzeugt werden, stehen der aufrufenden Prozedur nicht zur Verfügung, da diese gedropped werden, sobald der Batch Block beendet wird; genauso als würden Sie eine gespeicherte Prozedur beenden. Der Batch hingegen kann auf Tabellen zugreifen, die innerhalb der Prozedur erzeugt wurden.
• Verwenden Sie ein SET Befehl innerhalb des dynamischen SQL Batches, hat dieser nur für die Dauer des Batches Gültigkeit.
• Der Abfrageplan des dynamischen Batches ist nicht Teil des Planes der aufrufenden Prozedur. Für den Cache ist die Abfrage genauso gut als würden reine SQL Statements vom Client aus gesendet.
• Führt innerhalb des Batches eine Bedingung zum Abbruch, zum Beispiel Rollback in einem Trigger, wird nicht nur der dynamische Batch beendet, sondern auch die aufrufende Prozedur (und evtl. deren aufrufende Prozedur und so weiter...)

Im Unterschied zum regulären Aufruf einer gespeicherten Prozedur können Sie weder Parameter verwenden, noch erhalten Sie irgendwelche Rückgabewerte. Der @@error Wert bezieht sich auf das letzte Statement des Batches. Falls daher ein Fehler in EXEC() auftritt, dem ein erfolgreicher Befehl folgt, enthält @@error den Wert 0.

Der Befehl EXEC() wurde in der Version 6.0 des SQL Servers eingeführt.

Verwechseln Sie bitte nicht EXEC(@sql) mit EXEC @sp. Das Letztere führt eine gespeicherte Prozedur aus, deren Namen die Variable @sp enthält.

sp_executesql
sp_executesql wurde in SQL Server 7.0 eingeführt und hat den Vorteil, dass es Ihnen erlaubt ist, Parameter an den dynamischen SQL String zu übergeben, sowohl als Input wie auch als Output Parameter. Hier ist ein Beispiel für einen Output Parameter:

   DECLARE @sql nvarchar(4000),

           @col sysname,

           @min varchar(20)

   SELECT @col = N'au_fname'

   SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col +

                 N')) FROM authors'

   EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT

   SELECT @min

Sie können also wesentlich einfacher einen Wert aus Ihrem dynamischen SQL Statement erhalten und diesen einer lokalen Variablen zuweisen als mit EXEC() (Zwar können Sie dies auch mit EXEC() machen, indem Sie INSERT EXEC() benutzen, aber dies ist eher ein Hack als eine wirkliche Lösung.

Der erste Parameter von sp_executesql ist ein SQL Statement. Der Datentyp für diesen Parameter ist ntext, so dass Sie also eine Variable des Datentyps nvarchar verwenden müssen. Wenn Sie das SQL Statement als Literal übergeben, müssen Sie N vor das Anführungszeichen setzen, um anzugeben, dass es sich um Unicode Literal handelt. Das Statement kann Teile enthalten, die mit @ beginnen. Diese sind Parameter und nicht verbunden mit irgendwelchen anderen Variablen des umgebenden Gültigkeitsbereiches. Der SQL String ist nicht anders als jeglicher anderer SQL Text, das heisst, Sie können Parameter nur dort verwenden, an denen die SQL Syntax dies auch zulässt. Dass bedeutet, dass Sie keine Parameter für Spalten oder Tabellennamen verwenden können, wollen Sie diese dynamisch bestimmen, müssen Sie dies in Ihren String einbauen.

Der zweite Parameter, den sp_executesql erwartet, ist die Deklaration einer Parameterliste, mit der gleichen Syntax wie bei der Deklaration einer gespeicherten Prozedur, einschliesslich der Verwendung von Output Parametern und Standardwerten (Das Thema Output Parameter fehlt leider in der Online Hilfe). Auch diese Parameterliste ist vom ntext Datentyp. Alle Variablen, die Sie in Ihrem SQL Statement verwenden, müssen in der Parameterliste auftauchen.

Die übrigen Parameter von sp_executesql sind die, welche Sie in der Parameterliste definiert haben, in der Reihenfolge, in welcher sie auftauchen (Sie können auch benannte Parameter verwenden).

sp_executesql ist EXEC() aus einer Reihe von Gründen vorzuziehen. Einer davon ist, dass Sie mit sp_executesql sich nicht darauf verlassen müssen, dass SQL Server für Sie autoparametrisiert, da Sie die Parameter gleich mitliefern. Dadurch haben Sie bessere Chancen, für die Wiederverwendung aus dem Cache (Obwohl Sie immer noch auf Abweichungen bei den Leerzeichen achten müssen). Auf die anderen Vorteile von sp_executesql, möchte ich später eingehen, wenn ich SQL Injektion und Gute Kodierpraktiken diskutiere.

Folgende Punkte, die ich bei der Verwendung von EXEC() angeführt habe, gelten auch für sp_executesql:

• Der SQL Code hat seinen eigenen Gültigkeitsbereich und Sie haben keinen Zugriff auf die Variablen der aufrufenden gespeicherten Prozedur.
• Die Berechtigungen des aktuellen Benutzers werden angewendet.
• Jegliches USE Statement hat keine Auswirkung auf die aufrufende Prozedur.
• Temporäre Tabellen, die im SQL Batch erzeugt werden, sind nicht für die aufrufende Prozedur verfügbar.
• SET Befehle betreffen nur den Batch.
• Abbruch des durch sp_executesql ausgeführten Batches, bedeutet auch Abbruch der Prozedur.
• @@error gibt den Status des letzten Statements innerhalb des Batches wieder.

In der Online Hilfe steht, dass der Rückgabewert von sp_executesql entweder 0 für Erfolg oder 1 für Fehler ist. Aber es scheint, dass der Rückgabewert der letzte Wert von @@error ist, zumindest in SQL Server 2000.

Für eine vollständige Beschreibung von sp_executesql schauen Sie bitte in der Online Hilfe nach. Für eine Beschreibung des OUTPUT Features beachten Sie bitte den Knowledgebase Artikel 262499.

Die Qual der Wahl
Wenn Sie regelmässig dynamisches SQL verwenden, ist sp_executesql die bessere Wahl, da der Abfrageplan wiederverwendet werden kann und Parameter benutzt werden können. Abgesehen von dem offensichtlichen Fall, dass Sie noch mit SQL Server 6.5 arbeiten, gibt es nur noch eine weitere Situation, wo Sie EXEC() benötigen, und zwar dann, wenn Ihr dynamisches SQL Statement zu lang ist, um in eine nvarchar(4000) Variable zu passen. Für diesen Fall können Sie folgendes machen:

    EXEC(@sql1 + @sql2)

Wenn Sie sp_executesql aufrufen, können Sie nur Variablen verwenden, da T-SQL es nicht zulässt, dass Ausdrücke als Parameter an gespeicherte Prozeduren übergeben werden können. Wenn Sie nun unbedingt parametrisierte Abfragen verwenden wollen, können Sie sp_executesql in EXEC() einbetten:

   DECLARE @sql1 nvarchar(4000),

           @sql2 nvarchar(4000),

           @state char(2)

   SELECT @state = 'CA'

   SELECT @sql1 = N'SELECT COUNT(*)'

   SELECT @sql2 = N'FROM authors WHERE state = @state'

   EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',

                            N''@state char(2)'',

                            @state = ''' + @state + '''')

Dies funktioniert sogar, wenn Sie Output in Zusammenhang mit INSERT-EXEC verwenden. So wie hier:

   CREATE TABLE #result (cnt int NOT NULL)

   DECLARE @sql1  nvarchar(4000),

           @sql2  nvarchar(4000),

           @state char(2),

           @mycnt int

   SELECT @state = 'CA'

   SELECT @sql1 = N'SELECT @cnt = COUNT(*)'

   SELECT @sql2 = N'FROM authors WHERE state = @state'

   INSERT #result (cnt)

      EXEC('DECLARE @cnt int

            EXEC sp_executesql N''' + @sql1 + @sql2 + ''',

                               N''@state char(2),

                                  @cnt   int OUTPUT'',

                               @state = ''' + @state + ''',

                               @cnt = @cnt OUTPUT

            SELECT @cnt')

    SELECT @mycnt = cnt FROM #result

Vielleicht schreckt Sie das Labyrinth aus ineinander geschachtelten Anführungszeichen ab, so dass Sie nur EXEC() in diesem Fall verwenden. Sie können das obige Statement jedoch übersichtlicher mit der benutzerdefinierten Funktion quotestring() gestalten, die ich Ihnen später präsentieren möchte.

In SQL Server 2005 sollten Sie den neuen Datentypen nvarchar(max) für die @sql Variable verwenden. Dadurch vermeiden Sie es, sp_executesql zusammen mit EXEC() verwenden zu müssen
Cursor und dynamisches SQL

Cursor sind nun nicht gerade etwas, dass Sie häufig verwenden sollten, aber viele Leute fragen, wie dynamisches SQL mit Cursor funktioniert, so dass ich Ihnen der Vollständigkeit halber also ein Beispiel hierfür zeige. Da DECLARE CURSOR EXEC() nicht funktioniert, müssen Sie das gesamte DECLARE CURSOR Statement in dynamisches SQL setzen

   SELECT @sql = 'DECLARE my_cur CURSOR FOR SELECT col1, col2, col3 FROM ' + @table

   EXEC sp_executesql @sql

Beachten Sie in diesem Beispiel, dass Sie keinen lokalen Cursor verwenden können, da dieser verschwindet, sobald der dynamische Gültigkeitsbereich beendet wird. Anthony Faull hingegen wies mich daraufhin, dass Sie sehr wohl in der Lage sind, einen lokalen Cursor mit dynamischem SQL zu verwenden, und zwar indem Sie eine Cursor Variable benutzen, wie in diesem Beispiel

   DECLARE @my_cur CURSOR

   EXEC sp_executesql

         N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN @my_cur',

         N'@my_cur cursor OUTPUT', @my_cur OUTPUT

   FETCH NEXT FROM @my_cur

Sie verweisen auf eine Cursor Variable genauso wie Sie auf einen benannten Cursor verweisen, nur dass das @ Zeichen am Anfang steht. Wie Sie in den Beispiel gesehen haben, können Sie diese auch als Parameter übergeben (Ich muss zugeben, dass ich bis zu diesem Zeitpunkt keinen Verwendungszweck für Cursor Variablen hatte, bis Anthony Faull mir freundlicherweise dieses Beispiel übersandte).

Dynamisches SQL und gespeicherte Prozeduren
Gehen wir noch einmal einen Schritt zurück und betrachten die Gründe, warum wir gespeicherte Prozeduren verwenden und was passiert, wenn wir dynamisches SQL verwenden. Als Ausgangspunkt verwenden wir diese Prozedur:

   CREATE PROCEDURE general_select @tblname nvarchar(127),

                                   @key     key_type AS -- key_type is char(3)

       EXEC('SELECT col1, col2, col3

             FROM ' + @tblname + '

             WHERE keycol = ''' + @key + '''')

Wie wir sehen werden, ist dies eine vollkommen zwecklose Prozedur, da sie so gut wie alle Gründe zur Verwendung von gespeicherten Prozeduren nichtig macht. Genauso gut könnten Sie im Client Code ein SELECT Statement konstruieren und an den Server senden.

1. Berechtigungen 
Können Sie Ihren Benutzern nicht direkten Zugriff auf die Tabellen geben, können Sie kein dynamisches SQL verwenden. So einfach ist das. In einigen Umgebungen können Sie unterstellen, dass Benutzern SELECT Zugriff gewährt werden kann. Aber solange Sie nicht 100%ig wissen, dass Berechtigungen kein Problem darstellen, verwenden Sie dynamisches SQL nicht für INSERT, UPDATE und DELETE Statements. Ich sollte vielleicht noch schnell hinzufügen, dass dies nur für permanente Tabellen zutrifft. Falls Sie also nur auf temporäre Tabellen zugreifen, gibt es niemals Berechtigungsprobleme.

Wenn Sie Anwendungsrollen oder eine Zwischenschicht wie COM+ verwenden, also ohne direkten Zugriff der Benutzer auf die Datenbank, können Sie dieses Problem wahrscheinlich vernachlässigen. Nichtsdestoweniger gibt es Sicherheitsaspekte, die Sie berücksichtigen müssen, wie wir im Abschnitt SQL Injektion sehen werden.

Für den Fall, dass Sie Code schreiben, der von Systemadministratoren ausgeführt werden soll, brauchen Sie sich selbstverständliche keinerlei Gedanken, um irgendwelche Berechtigungen zu machen.

2. Abfragepläne cachen 
Wie wir gesehen haben, cached SQL Server Abfragepläne sowohl für reine SQL Statements als auch für gespeicherte Prozeduren, ist aber verlässlicher bei der Wiederverwendung von Abfrageplänen gespeicherter Prozeduren. Während Sie in SQL Server 6.5 eindeutig sagen konnten, dass dynamisches SQL langsamer war, da jedes Mal erneut kompiliert werden musste, begannen diese Grenzen mit den nachfolgenden Versionen allmählich zu verschwimmen.

Schauen wir uns die Prozedur general_select weiter oben an, erkennen wir, dass der Abfrageplan gecached und autoparametrisiert wird für jeden Wert der Variable @tblname, den Sie übergeben. Nochmals, genauso gut können Sie so ein SQL Statement im Client Code erzeugen.

Dieses vorausgesetzt, ist es sinnvoll, darauf hinzuweisen, dass der umsichtige Einsatz von dynamischem SQL sehr wohl Performancesteigerungen bringen kann. Zum Beispiel, mal angenommen, Sie haben eine komplexe Abfrage in der Mitte einer umfangreichen gespeicherten Prozedur, bei der der "beste" Abfrageplan sehr stark von den Daten abhängt, die sich zurzeit gerade in der Tabelle befinden. Sie können diese Abfrage nun in dynamisches SQL umwandeln und darauf hoffen, dass SQL Server smart genug ist, um nicht den Cache zu verwenden (falls eine temporäre Tabelle involviert ist, ist es nicht wahrscheinlich, dass dies passiert). Nun, das gleiche Ergebnis erreichen Sie, indem Sie diese komplexe Abfrage in eine eigene gespeicherte Prozedur auslagern, obwohl der Code vielleicht einfacher zu lesen sein mag, wenn die gesamte Programmlogik an einer Stelle zusammengefasst ist. Voraussetzung bei allem ist natürlich, dass Ihre Berechtigungspolitik den Gebrauch von dynamischem SQL erlaubt.

3. Netzwerkverkehr minimieren 
In den beiden vorangegangenen Abschnitten haben wir gesehen, dass dynamisches SQL in einer gespeicherten Prozedur nicht besser ist, als reine SQL Statements, die vom Client kommen. Bezogen auf den Netzwerkverkehr sieht dies nun gänzlich anders aus. Dynamisches SQL innerhalb von gespeicherten Prozeduren verursacht keine Netzwerkkosten. Wenn wir wieder unsere Beispielprozedur general_select betrachten, haben wir allerdings nicht viel gewonnen. Der Prozeduraufruf verbraucht fast genauso viele Bytes wie reiner SQL Code.

Aber angenommen, Sie haben eine komplexe Abfrage, die einen JOIN über sechs Tabellen mit komplexen Bedingungen ausführt, wobei einer dieser Tabellen sales0101, sales0102 etc. sein kann, je nachdem für welche Periode der Benutzer Daten abfragen will. Für den Moment vernachlässigen wir die Tatsache, dass dies schlechtes Tabellendesign ist, auf das wir später zurückkommen werden. Für den Augenblick nehmen wir an, dass Sie an dieses Design gebunden sind. Wenn Sie dieses Problem durch eine gespeicherte Prozedur mit dynamischem SQL lösen wollen, brauchen Sie nur jedes Mal die Periode als Parameter zu übergeben, anstelle der gesamten Abfrage. Falls die Abfrage einmal pro Stunde ausgeführt wird, ist der Gewinn kaum merklich. Wenn die Abfrage aber alle 5 Sekunden ausgeführt wird, ist es sehr wahrscheinlich, dass Sie einen Unterschied bemerken werden.

4. Verwenden von OUTPUT Parametern 
Falls Sie eine gespeicherte Prozedur nur deshalb schreiben, um vom Output Parameter zu profitieren, so wird dies in keinster Weise durch dynamisches SQL beeinflusst. Nochmals, Sie erhalten Output Parameter auch ohne gespeicherte Prozedur, indem Sie sp_executesql direkt vom Client aus ausführen.

5. Logik kapseln 
Unser ersten Betrachtung ist in diesem Punkt nicht viel hinzuzufügen. Ich möchte noch einmal deutlich machen, dass, falls Sie sich für den Einsatz gespeicherter Prozeduren entscheiden, alle "Geheimnisse" in diesen Prozeduren liegen sollten, so dass die Übergabe des Tabellennamens wie in general_select keine gute Idee ist. Einzige Ausnahme hier sind Systemadministratoren Hilfsmittel.

6. Nachhalten, was verwendet wird 
Dynamisches SQL ist das genaue Gegenteil zu diesem Ansatz. Jede Verwendung von dynamischem SQL verbirgt die Referenzierungen, so dass diese nicht in sysdepends auftauchen. Ferner offenbart sich die Referenz auch nicht selber, wenn Sie eine Datenbank erstellen ohne das referenzierte Objekt. Wenn Sie aber davon absehen, Tabellen- oder Spaltennamen als Parameter zu übergeben, besteht zumindest die Möglichkeit, den SQL Code zu durchsuchen, um herauszufinden, ob eine bestimmte Tabelle benutzt wird oder nicht. Also, falls Sie dynamisches SQL verwenden, belassen Sie zumindest Tabellen- und Spaltennamen im Code der Prozedur.

SQL Injektion - ein ernstes Sicherheitsproblem

Unter SQL Injektion versteht man eine Technik, durch die ein Angreifer versucht, Ihren SQL Code als Hilfsmittel zu benutzen, um vollkommen anderen Code auszuführen, als Sie dies beabsichtigt haben. Sie werden anfällig für SQL Injektion, sobald Sie Benutzereingaben direkt an Ihren SQL Code weitergeben, gleichgültig ob dies dynamisches SQL in einer gespeicherten Prozedur ist, oder im Client Code generierte SQL Statements. Diese Art von Attacken ist nicht nur auf SQL Server beschränkt, sondern stellt für alle RDBMS ein Problem dar.

Betrachten Sie diese Prozedur:

   CREATE PROCEDURE search_orders @custname varchar(60) = NULL,

                                  @prodname varchar(60) = NULL AS

   DECLARE @sql nvarchar(4000)

   SELECT @sql = 'SELECT * FROM orders WHERE 1 = 1 '

   IF @custname IS NOT NULL

      SELECT @sql = @sql + ' AND custname LIKE ''' + @custname + ''''

   IF @prodname IS NOT NULL

      SELECT @sql = @sql + ' AND prodname LIKE ''' + @prodname + ''''

   EXEC(@sql)

Angenommen, der Input der Parameter @custname und @prodname kommt direkt aus Feldern, die vom Benutzer editierbar sind. Nehmen Sie weiterhin an, dass ein böswilliger Benutzer diesen Wert der Variable @custname übergibt:

   ' DROP TABLE orders --

Das daraus resultierende SQL Statement sieht folgendermassen aus:

      SELECT * FROM orders WHERE 1 = 1  AND custname LIKE '' DROP TABLE orders --'

Sehen Sie den rot markierten Text? Ob diese Attacke Erfolg hätte, ist schwer zu sagen. Ein einfacher Benutzer, der sich direkt am SQL Server angemeldet hat, hat wahrscheinlich nicht die Berechtigungen, eine Tabelle zu löschen. Ist er aber ein Webbenutzer und Ihr Webserver verbindet sich mit Administratorprivilegien zum SQL Server, hätte die Attacke Erfolg. Falls er nicht die Berechtigung für diese präzise Attacke besitzt, ist er aber vielleicht dennoch in der Lage, Zugriff zu erlangen und weitere Befehle auszuführen.

Die Strategie hinter dieser Attacke ist es, dass der Angreifer zunächst testet, was passiert, wenn er die einfachen Anführungszeichen in das Eingabefeld eingibt. Produziert dies einen Syntaxfehler, weiss er, dass es hier eine Schwachstelle gibt. Anschliessend versucht er herauszufinden, welche weiteren Zeichen er eingeben muss, um die Abfrage zu beenden, um dann sein eigenes Statement auszuführen. Zu guter Letzt fügt er ein Kommentarzeichen hinzu, um den Rest der Abfrage zu unterbinden und Syntaxfehler zu vermeiden. Ebenfalls könnte er versuchen, ein Semikolon zu benutzen, welches in SQL Server 7.0 und höher als optionaler Statement Terminator dient. Falls er entdeckt, dass dies einen Programmfehler verursacht, ist er vielleicht über etwas wie general_select gestolpert. Wenn dessen Parameter @tblname durch etwas gefüllt wird, dass frei vom Benutzer eingebbar ist, kann der Angreifer folgenden String senden:

   some_table WHERE keycol = 'ABC' DELETE orders

Bedenken Sie bitte, dass nicht nur direkte Eingabefelder Angriffspunkte sind. Wenn Sie Parameter in Ihren URLs direkt als Argumente an gespeicherte Prozeduren senden, kann ein Angreifer auch versuchen, diese anzugreifen.

Nun mögen Sie vielleicht denken, dass dies nicht nur Geschick sondern auch Glück erfordert, ein solches Sicherheitsloch zu entdecken. Vergessen Sie dabei nicht, dass es irgendwo da draussen zu viele Hacker mit zu viel freier Zeit gibt. SQL Injektion ist ein ernstes Sicherheitsthema, gegen dass Sie sich schützen müssen. Es gibt zwei verschiedene Verteidigungsmechanismen.

Geben Sie Ihren Benutzern nicht mehr Berechtigungen als zwingend notwendig. Falls Ihre Applikation sich von einer Zwischenschicht in SQL Server einloggt, sollte sie das als einfacher Benutzer tun, der höchstens SELECT Berechtigungen auf den Tabellen besitzt. Begründung hierfür ist, dass ein unerfahrener oder nachlässiger Programmierer an irgendeinem Punkt einen Weg für SQL Injektion öffnen könnte. 
Es existieren sehr einfache, aber effiziente Kodierungsmechanismen, die Sie verwenden können. Wir werden diese im nächsten Abschnitt über gute Kodierpraktiken näher untersuchen. 
Ich sollte betonen, dass SQL Injektion nicht nur auf gespeicherte Prozeduren beschränkt ist, vielmehr ist Client Code, in dem Sie SQL Code generieren noch gefährdeter, da Ihre String Variablen oftmals nicht beschränkt sind. Bedenken Sie bitte, dass, obwohl Sie gespeicherte Prozeduren verwenden, Sie deren Aufrufe vielleicht als EXEC() Statements generieren, die Sie als Text übergeben. Dies sind natürlich ebenfalls Angriffspunkte für SQL Injektion.

Gute Kodierpraktiken und dynamisches SQL

Obwohl es vielleicht einfach erscheinen mag, dynamisches SQL zu schreiben, ist dies jedoch eine Aufgabe, die Disziplin erfordert, wenn Sie nicht die Kontrolle über Ihren Code verlieren wollen. Wenn Sie einfach drauf losschreiben, kann Ihr Code sehr schwer zu lesen und zu warten werden. Betrachten wir diese schreckliche general_select Prozedur:

   CREATE PROCEDURE general_select @tblname nvarchar(127),

                                   @key     key_type AS -- key_type is char(3)

       EXEC('SELECT col1, col2, col3

             FROM ' + @tblname + '

             WHERE keycol = ''' + @key + '''')

Ihnen sind wahrscheinlich die vielen einfachen Anführungszeichen aufgefallen und vielleicht haben Sie sich gefragt, was diese bedeuten sollen? SQL ist eine der Sprachen, deren Methode es ist, einen String Delimiter selber in einen String Delimiter einzuschliessen, diesen einfach doppelt anzugeben. So sind also diese vier aufeinanderfolgenden einfachen Anführungszeichen ('''') ein String Literal mit einem Wert von einem einfachen Anführungszeichen. Dies ist nur ein einfaches Beispiel, es kann wesentlich schlimmer kommen.

Ein weiterer Fehler, den Sie leicht begehen können ist:

    EXEC('SELECT col1, col2, col3

          FROM' + @tblname + '

          WHERE keycol = ''' + @key + '''')

Sehen Sie, dass hinter dem FROM ein Leerzeichen fehlt? Wenn Sie diese Prozedur kompilieren, erhalten Sie keine Fehlermeldung, aber wenn Sie diese ausführen, wird Ihnen mitgeteilt, dass die Spalten keycol, col1, col2, col3 fehlen. Da Sie aber wissen, dass die Tabelle, deren Namen Sie übergeben haben, diese Spalten enthält, werden Sie vielleicht mächtig verwirrt sein. Aber der tatsächlich generierte Code sieht folgendermassen aus, unter der Annahme der Parameterwerte foo und abc:

   SELECT col1, col2, col3 FROMfoo WHERE keycol = 'abc'

Dies ist tatsächlich kein Syntaxfehler, da FROMfoo als Spalten Alias für col3 interpretiert wird.

Ich habe bereits meine Meinung ausgedrückt, dass Sie niemals Spalten- oder Tabellennamen als Parameter übergeben sollen, aber da dies der Abschnitt über gute Kodierpraktiken ist, möchte ich es noch einmal wiederholen. Sobald Sie beginnen, gespeicherte Prozeduren zu schreiben, sollte dies der einzige Ort sein, an dem Sie SQL Server Objekte referenzieren (Stichwort: Sichere gespeicherte Prozeduren!). Nichtsdestotrotz ist hier eine aufpolierte Version von general_select, die einige gute Kodier Tugenden für dynamischen SQL demonstriert:

   CREATE PROCEDURE general_select @tblname nvarchar(128),

                                   @key key_type,

                                   @debug bit = 0 AS

   DECLARE @sql nvarchar(4000)

   SET @sql = 'SELECT col1, col2, col3

               FROM ' + quotename(@tblname) + '

               WHERE keycol = @key'

   IF @debug = 1 PRINT @sql

   EXEC sp_executesql @sql, N'@key key_type', @key = @key

Wie Sie sehen, habe ich eine Reihe von Änderungen vorgenommen:

• Ich umhülle @tblname in quotename(), um mich vor SQL Injektion zu schützen. Mehr zu quotename() weiter unten.
• Ich habe einen @debug hinzugefügt. Für den Fall, dass ein unerwarteter Fehler auftritt, kann ich auf diese schnell herauszufinden, wie mein SQL Code denn nun tatsächlich aussieht.
• Statt den Wert von @key in den String mit einzuschliessen, benutze ich sp_executesql und übergebe @key als Parameter. Auch dies schützt mich vor SQL Injektion.

quotename() ist eine eingebaute Funktion, die erstmals in SQL Server 7.0 eingeführt wurde. Sie gibt ihren Input umgeben von den angegebenen String Delimitern zurück. Eingebettete Delimiter werden verdoppelt. Der Standarddelimiter sind die eckigen Klammern, da es der Hauptzweck der Funktion ist, Objektnamen zu benennen. Sie können aber auch einfache Anführungszeichen angeben. Falls Sie also aus irgendeinem Grund EXEC() vorziehen, können Sie quotename() verwenden, um sich vor SQL Injektion zu schützen. Nehmen wir zum Beispiel eine Zeile aus der Beispielprozedur search_order aus dem Abschnitt über SQL Injektion, können wir diesen folgendermassen modifizieren:

   IF @custname IS NOT NULL

      SELECT @sql = @sql + ' AND custname LIKE ' + quotename(@custname, '''')

Auf eine Sache müssen Sie aber bei quotename() achten. Der Inputparameter ist ein nvarchar(129), so dass diese Funktion keinen langen String bearbeiten kann. In SQL Server 2000 können Sie stattdessen diese benutzerdefinierte Funktion benutzen.

   CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS

   BEGIN

      DECLARE @ret nvarchar(4000),

              @sq  char(1)

      SELECT @sq = ''''

      SELECT @ret = replace(@str, @sq, @sq + @sq)

      RETURN(@sq + @ret + @sq)

   END

Ersetzen Sie 1998 und 4000 durch MAX, falls Sie SQL Server 2005 verwenden. Die Funktion wird wie folgt eingesetzt:

   IF @custname IS NOT NULL

      SELECT @sql = @sql + ' AND custname LIKE ' + dbo.quotestring(@custname)

In SQL Server 7.0 müssen Sie quotestring() als gespeicherte Prozedur implementieren. SQL Server 6.5 verfügt über keine replace(), so dass Sie hier wenig Chancen haben. Ich möchte hinzufügen, dass der Vorschlag quotename() oder eine benutzerdefinierte Funktion zu verwenden, vom SQL Server MVP Steve Kass kam.

Eine andere Alternative, um dem Gewirr von verschachtelten Anführungszeichen zu entkommen, ist Gebrauch zu machen von der Tatsache, dass T-SQL eigentlich über zwei String Delimiter verfügt. Wenn Sei die Einstellung QUOTED_IDENTIFIER OFF schalten, können Sie auch doppelte Anführungszeichen (") verwenden. Der Standard für diese Einstellung hängt vom Gesamtzusammenhang ab, die bevorzugte Einstellung ist aber o­n, und sie muss auch o­n sein, um indizierte Sichten und Indizes auf berechneten Spalten benutzen zu können. Nun, dies ist zwar keine erstklassige Alternative, aber wenn Sie sich der Vorbehalte bewusst sind, können Sie folgendes machen:

   CREATE PROCEDURE general_select @tblname nvarchar(127),

                                   @key     key_type,

                                   @debug   bit = 0 AS

   DECLARE @sql nvarchar(4000)

 

   SET @sql = 'SET QUOTED_IDENTIFIER OFF

               SELECT col1, col2, col3

               FROM ' + @tblname + '

               WHERE keycol = "' + @key + '"'

   IF @debug = 1 PRINT @sql

   EXEC(@sql)

Da jetzt zwei verschiedene Anführungszeichen verwendet werden, ist der Code viel leichter zu lesen. Die einfachen Anführungszeichen sind für den SQL String, während die doppelten für die eingebetteten String Literale sind. 

Alles in allem ist dies eine minderwertige Methode sp_executesql und quotename() zu verwenden, da Sie nicht gegen SQL Injektion geschützt sind. Für administrative Aufgaben hingegen (wo SQL Injektion sehr wahrscheinlich kein Thema ist) ist es in Ordnung, wahrscheinlich der beste Weg in SQL Server 6.5.

Lassen Sie mich diesen Abschnitt damit beenden, dass auch wenn Sie den Richtlinien, die ich Ihnen hier vorgestellt habe, folgen, die Komplexität, die Sie zu Ihrem SQL Code durch dynamischen SQL hinzufügen, bemerkenswert ist, so dass Sie stets zweimal überlegen sollten, bevor Sie sich für diesen weg entscheiden.

Beliebte Beispiele, dynamisches SQL (nicht) zu benutzen

Wenn Sie den verschiedenen Newsgroups über SQL Server folgen, finden Sie fast jeden Tag jemanden, der eine Frage stellt, die mit "Benutz' dynamisches SQL" beantwortet wird, zusammen mit einem schnellen Beispiel. Aber sehr oft vergisst die antwortende Person etwas über die Implikationen auf Berechtigungen und Caching zu sagen. Und obwohl für viele dieser Fragen tatsächlich keine andere Antwort als dynamisches SQL besteht, ist es oft so, dass zu diesem Geschäftsproblem oft ein komplett anderer Lösungsansatz existiert - und ein viel besserer zugleich.

Daher möchte ich diesem Abschnitt auf einige Fälle eingehen, bei denen Sie dynamisches SQL verwenden können. Für einige ist dynamisches SQL tatsächlich ein guter Ansatz, für andere Fälle hingegen eine schlechte Wahl, und für wieder andere Fälle entsteht die Frage nur deshalb, weil ein falscher Lösungsansatz gewählt wurde.

select * from @tablename
Eine beliebte Frage ist, warum dies nicht funktioniert:

   CREATE PROCEDURE my_proc @tablename sysname AS

      SELECT * FROM @tablename

Wie wir gesehen haben, können wir eine solche Prozedur mit dynamischem SQL erstellen, wir haben aber auch gesehen, dass das Resultat eine komplette sinnlose Prozedur wäre. Falls dies Ihre Vorstellung von SQL Programmierung ist, brauchen Sie sich um die Verwendung gespeicherter Prozeduren keine ernsthaften Gedanken zu machen.

Es scheint verschiedene Gründe zu geben, warum diese Frage so beliebt ist. Das eine Lager scheint aus Leuten zu bestehen, die neu in der SQL Programmierung sind, aber in anderen Programmiersprachen wie C++ oder VB etc., in denen Parametrisierung ein guter Ansatz sind, bereits Erfahrungen haben. Parametrisierung des Tabellennamens, um generischen Code zu erhalten und damit die Wartbarkeit zu erhöhen, ist einfach eine gute Programmiertugend.

Aber gerade dann, wenn es zu Datenbankobjekten kommt, ist diese Regel nicht gültig. Sie sollten jede Tabelle und Spalte als eigenes konstantes Objekt begreifen. Warum? Wenn es an die Erstellung eines Abfrageplanes geht, hat jede Tabelle ihre eigenen Statistiken und Eigenheiten und diese sind für SQL Server keinesfalls austauschbar. Und in einem komplexen Datenmodell ist es besonders wichtig, den Überblick zu bewahren, was wo verwendet wird. Wenn Sie damit beginnen, Tabellen und Spaltennamen als Parameter zu übergeben, verlieren Sie garantiert die Kontrolle.

Wenn Sie also obiges einsetzen wollen (mal abgesehen von der Tatsache, dass SELECT * sowieso nichts in Produktionscode verloren hat), um sich Tipparbeit zu sparen, sind Sie auf dem falschen Weg. Es ist wesentliche besser, 10 oder auch 20 gespeicherte Prozeduren zu schreiben, auch wenn diese sich sehr ähnlich sind.

Wenn Ihre SQL Statements komplex sind, so dass hier tatsächlich ein Vorteil darin liegen könnte, aus Gründen der Wartbarkeit, alles an einem Ort zu verwalten, gibt es nur eine Strategie, die Sie in Betracht ziehen können. Falls Sie nicht verschiedene Tabellen verwenden, können Sie eine Präprozessor wie den in C/C++ benutzen. Sie müssten auch weiterhin einen Satz gespeicherter Prozeduren pro Tabelle benutzen, der Code selber wäre aber in einer einzigen include Datei.

select * from sales + @yymm
Dies ist eine Variation des vorangegangenen Beispiels. Der Unterschied liegt darin, dass ich im obigen Beispiel angenommen habe, dass es eine endliche Menge von Tabellen gibt. Aber einige Leute scheinen Systeme zu haben, in denen Tabellen dynamisch erzeugt werden. Zum Beispiel wird jeden Monat eine neue Tabelle erzeugt, mit den entsprechenden Umsatzdaten. In diesem Fall ist der Vorschlag einer Prozedur pro Tabelle nicht wirklich gültig, nicht einmal mit Präprozessor.

Gibt es also keinen anderen Ausweg als dynamisches SQL? Doch, gehen wir noch einmal einen Schritt zurück und betrachten noch einmal die Situation. Hier wird von Beginn an der falsche Ansatz verfolgt. Ihr Datenmodell ist einfach fehlerhaft. Eine Tabelle pro Monat mag in Systemen wie Access oder Flat File Datenbanken notwendig sein, um die Performance zu erhöhen, aber in SQL Server oder jedem anderen High-End RDBMS gibt es kaum einen Grund für diesen Ansatz. SQL Server und seine Konkurrenzprodukte sind entwickelt worden für den Umgang mit grossen Datenmengen und den Zugriff darauf, anhand der Schlüssel in den Daten. Jahr und Monat sollte schlicht und einfach der erste Teil des Primärschlüssels einer einzigen Umsatztabelle.

Haben Sie ein 'Legacy' System, können die Kosten für eine Remodellierung abschreckend sein (Obwohl Sie auch die Kosten für den weitaus komplexeren SQL Code, den dynamisches SQL mit sich bringt, einkalkulieren müssen). Falls Sie ein neues System entwickeln, vergessen sie einfach alles über dynamisch erstellte Tabellen. Ihr Code für den Zugriff und die Aktualisierung dieser Tabellen wird einfach haarsträubend sein. Und wenn Sie diese Tabellen häufig erstellen, zum Beispiel eine Tabelle für jeden Einkaufswagen in einem eCommerce System, erstellen Sie wahrscheinlich einen Brennpunkt und Flaschenhals, der nachteilig für die Performance ist.

Vielleicht sind Sie immer noch nicht überzeugt und denken Ich werde aber Millionen von Zeilen haben. Die Datenbank wird nicht funktionieren, wenn alle Daten in einer einzigen Tabelle sind. Ok. falls Sie tatsächlich viele Zeilen in einer Tabelle haben, haben Sie Gründe nachzudenken. Aber nicht, wenn Sie nur mehrere Millionen Zeilen haben. Das sind Alltagsaufgaben für den SQL Server. Voraussetzung dafür ist natürlich die intelligente Definition Ihrer Indizes. Wenn Sie mehrere 100 Millionen Zeilen haben, dann sollten Sie sich dies einmal genauer ansehen. Für diese Bereiche bietet SQL Server 2000 Features wie partitionierte Sichten, oder sogar verteilte partitionierte Sichten, die es Ihnen ermöglichen, grossen Datenmengen auf mehrere Tabellen aufzuteilen, darauf aber wie auf eine einzige Tabelle zuzugreifen (Beachten Sie bitte, dass ich aus Vereinfachungsgründen einfach eine Zeilenanzahl angenommen habe. Was tatsächlich ausschlaggebend ist, ist die Gesamtgrösse der Tabelle, die von der durchschnittlichen Zeilengrösse anhängt.

update tbl set @colname = @value where keycol = @keyval

In diesem Fall möchten Sie eine Tabelle aktualisieren, die Sie erst zur Laufzeit bestimmen. Der obige Code ist absolut gültig in T-SQL, was aber passiert ist, dass der Variable @colname der Wert der Variable @value zugeordnet wird, und zwar für jede betroffene Zeile in der Tabelle.

In diesem Fall fordert das dynamische SQL dass die Benutzer UPDATE Berechtigungen auf den Tabellen besitzen, etwas was nicht leichtfertig abgehakt werden sollte. Alle Gründe sprechen dagegen. Hier ist ein sehr einfacher alternativer Ansatz:

   UPDATE tbl

   SET    col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END,

          col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END,

          ...

Falls Sie nicht mit dem CASE Statement vertraut sind, lesen Sie bitte in der o­nlinehilfe nach. Dies ist ein sehr mächtiger SQL Befehl.

Wiederum mag man sich fragen, warum manche Leute dies wollen. Vielleicht, weil Ihre Tabellen folgendermassen aussehen:

    CREATE TABLE products (prodid   prodid_type NOT NULL,

                           prodname name_type   NOT NULL,

                           ...

                           sales_1  money       NULL,

                           sales_2  money       NULL,

                           ...

                           sales_12 money       NULL,

                           PRIMARY KEY (prodid))

Mehr Sinn würde es machen, diese Umsatz_n Spalten in eine zweite Tabelle auszulagern:

    CREATE TABLE product_sales (prodid prodid_type NOT NULL,

                                month  tinyint     NOT NULL,

                                sales  money       NOT NULL,

                                PRIMARY KEY (prodid, month))

select * from @dbname + '..tbl'
In diesem Fall ist die Tabelle in einer anderen Datenbank, die dynamisch bestimmt wird. Es gibt mehrere Gründe, warum manche Leute dies wollen, und abhängig von dem Grund gibt es verschiedene Lösungen.

Zugriff auf Daten einer anderen Datenbank. Falls Sie aus irgendeinem Grund Ihre Applikation auf zwei Datenbanken verteilt haben, sollten Sie Ihren Code nicht durch festverdrahtete Referenzierungen auf die Datenbanknamen verschandeln, da Sie an dem Tag, an dem Sie ein Testsystem auf dem gleichen Server aufbauen wollen, Probleme kriegen. Eine Idee könnte sein, den Namen der anderen Datenbank aus einer Konfigurationstabelle auszulesen und daraus ein dynamisches Statement zu konstruieren. Auch hier gibt es allerdings eine andere Alternative, die aber voraussetzt, dass Sie die Operationen, für die Sie andere Datenbank benötigen, in einer einzigen Prozedur unterbringen können. In diesem Fall können Sie folgendes machen:

   SET @sp = @dbname + '..some_sp'

   EXEC @ret = @sp @par1, @par2...

Der Name der gespeicherten Prozedur ist in der Variablen @sp.

Etwas in jeder Datenbank ausführen
Mir erscheint dies als eine Art sysadmin Abenteuer, und für sysadmin Aufgaben ist dynamisches SQL durchaus erlaubt, da weder Caching noch Berechtigungen ein Thema sind. Nichtsdestotrotz existiert auch hier eine Art von Alternative, sp_MSforeachdb, wie dieses Beispiel zeigt:

    sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects'

Wie Sie vielleicht erraten können, verwendet sp_MSforeachdb intern ebenfalls dynamisches SQL, so dass Ihr Gewinn nur darin liegt, dass Sie die Kontrollschleife nicht selber schreiben müssen. Ich möchte hinzufügen, dass sp_MSforeachdb nicht in der Online Hilfe dokumentiert ist, was bedeutet, dass der Gebrauch nicht von Microsoft unterstützt wird.

Eine "Master" Datenbank. 
Gelegentlich begegne ich Leuten, die eine grosse Anzahl von Datenbanken verwalten, die alle die gleiche Tabellenstruktur besitzen. Teilweise betreiben diese Leute Providerservices und jede Datenbank ist für einen anderen Kunden. Aus geschäftlichen Gründen können diese Datenbanken nicht in einer einzigen zusammengefasst werden, so dass man ein Problem in der Wartung sämtlicher Datenbanken sehen kann. Aus diesen Gründen möchten Sie eine Art "Master" Datenbank für Ihre gespeicherten Prozeduren. Diese Lösung würde aber bedeuten, dass sämtliche Prozeduren dynamisch sind, was ebenfalls zu einem Wartungsalptraum führt.

Zwei mögliche Lösungen bieten sich hier an. Die eine ist, SQL Server's eigene master Datenbank zu verwenden und die Applikationsprozeduren als Systemprozeduren zu installieren. Dies wird natürlich nicht von Microsoft unterstützt und zieht Sicherheitsimplikationen nach sich, weshalb ich Ihnen von dieser Alternativen abraten möchte.

Der andere Weg? Installieren Sie die gespeicherten Prozeduren in jeder Datenbank und entwickeln Sie Ihre eigenen Verteilungsroutinen. Früher oder später benötigen Sie solche Routinen sowieso, da Sie vielleicht einmal Änderungen an den Tabellen vornehmen müssen. Dadurch dass Sie die Prozeduren in jeder Datenbank haben, können Sie geizige Kunden nicht auf neue Versionen upgraden, oder für wichtige Kunden individuelle Features installieren. Nun, wie Sie Verteilungsroutinen installieren, bringt uns zum Thema Konfiguration Management, das weit über den Bereich dieses Artikels hinausgeht. Lassen Sie mich nur zwei Hinweise einstreuen: das SQL Server Resource Kit beinhaltet einen Assistenten für gespeicherte Prozeduren, SQL Server Objekte aus Visual Source Safe heraus zu installieren. Ich selber kann AbaPerls anbieten, eine Reihe von Tools, die ich über die Jahre hinweg entwickelt habe, um die Bedürfnisse in meiner Firma abzudecken. Sie finden diese unter http://www.abaris.se/abaperls/. Sie sind Freeware.

select * from tbl where col in (@list)

Eine sehr häufige Frage und Benutz dynamisches SQL ist die bei weitem häufigste Antwort. Dabei ist dynamisches SQL einfach der falsche Weg hier. Es gibt keinen Grund, für diese Art von Abfragen, SELECT Berechtigung aus den Tabellen zu vergeben, und falls diese Liste viele Elemente enthält, verschlechtert sich ausserdem noch Ihre Performance.

Die Alternative? Entpacken Sie Ihren String in eine Tabelle mit Hilfe einer benutzerdefinierten Funktion oder einer gespeicherten Prozedur. Ich möchte hier keine Beispiele dafür anführen, da ich einen Artikel http://www.sommarskog.se/arrays-in-sql.html geschrieben habe, in dem ich zahlreiche Wege, diesem Problem zu begegnen erörtere und auch Performance Daten der verschiedenen Methoden vergleiche (Dynamisches SQL ist am unteren Ende dieser Liste). Es ist ein sehr langer Artikel, aber von der Inhaltsangabe können Sie über Links schnell zu der für Sie relevanten Stelle gelangen, je nachdem mit welcher SQL Server Version Sie arbeiten.

select * from tbl where @condition
Falls Sie vorhaben, folgende Prozedur zu schreiben:

   CREATE PROCEDURE search_sp @condition varchar(8000) AS

      SELECT * FROM tbl WHERE @condition

Vergessen Sie es. Wenn Sie dies tun, haben Sie den Schritt zur Verwendung gespeicherter Prozeduren nicht komplett abgeschlossen, und assemblieren Ihren SQL Code immer noch im Client. Aber dieses Beispiel bringt uns zu

Dynamische Suchbedingungen
Ein nicht zu seltener Fall ist, dass Benutzer in der Lage sein sollen, Daten anhand einer breiten Anzahl von Parametern zu selektieren. Jeder Programmierer wird realisieren, dass der Versuch eine statische Lösung mit einer massgeschneiderten Abfrage für jede mögliche Kombination der Inputparameter unmöglich ist. Und die meisten Programmierer werden vermuten, dass der Versuch alle Bedingungen in einer einzigen Abfrage unterzubringen mit Hilfe von „smartem" SQL keine gute Performance bringen wird.

Ja, dies ist die Art von Problemen, wo dynamisches SQL der klare Gewinner ist. Solange das Berechtigungsthema für Sie kein Problem ist, benutzen Sie dynamisches SQL. Die Performance wird akzeptabel sein und die Wartungskosten überschaubar sein. Ich habe einen weiteren Artikel Dynamische Suchbedingungen, in dem ich Beispiele anführe, wie Sie diese Art von Abfragen mit dynamischem und statischem SQL verwirklichen.

select * from table order by @col
Dies kann leicht ohne dynamisches SQL so verwirklicht werden:

     SELECT col1, col2, col3

     FROM   tbl

     ORDER  BY CASE @col1

                  WHEN 'col1' THEN col1

                  WHEN 'col2' THEN col2

                  WHEN 'col3' THEN col3

               END

Nochmals, schauen Sie sich die Ausführungen zu CASE in der o­nlinehilfe an, falls Sie nicht damit vertraut sind.

Beachten Sie bitte, dass Spalten nicht in einem CASE Statement untergebracht werden können, wenn diese unterschiedliche Datentypen haben. Dieses Problem können Sie folgendermassen angehen:

     SELECT col1, col2, col3

     FROM   tbl

     ORDER  BY CASE @col1 WHEN 'col1' THEN col1 ELSE NULL END,

               CASE @col1 WHEN 'col2' THEN col2 ELSE NULL END,

               CASE @col1 WHEN 'col3' THEN col3 ELSE NULL END

SQL Server MVP Itzik Ben-Gan hat einen guten Artikel zu diesem Thema in der März 2001 Ausgabe des SQL Server Magazine veröffentlicht, in dem er weitere Lösungen anbietet.

select top @n from table order by @col
Hier ist ein einfacher Weg, dies ohne dynamisches SQL zu erreichen:

     CREATE PROCEDURE get_first_n @var int WITH RECOMPILE AS

     SET ROWCOUNT @var

     SELECT *

     FROM   authors

     ORDER  BY au_id

     SET ROWCOUNT 0

Vielleicht haben Sie irgendwo gelesen, dass der Optimierer sich nicht um SET ROWCOUNT kümmert. Dies trifft für SQL Server 6.5 zu, wo Sie kein TOP Prädikat haben, so dass Sie sowieso keine andere Wahl haben. Aber in SQL Server 7.0 und höher, kümmert sich der Optimierer um SET ROWCOUNT. Trotzdem müssen Sie sein, einen Parameter (und keine lokale Variable) als Argument für SET ROWCOUNT zu verwenden, da anderenfalls der Optimierer den Wert nicht kennt und sich deshalb eventuell für einen Tabellenscan entscheidet.

Vorsicht ist auch geboten SET ROWCOUNT 0 nach dem SELECT Statement zu setzen, da dadurch andere Statements in der Prozedur betroffenen sein könnten.

Die Online Hilfe beschreibt einige Vorbehalte über SET ROWCOUNT und rät davon ab, dies mit DELETE, INSERT und UPDATE Statements zusammen zu verwenden. Den genauen Grund hierfür kenne ich auch nicht, aber ich vermute, dass INSERT in eine temporäre Tabelle mit SET ROWCOUNT in Ordnung ist. INSERT in eine Tabelle durch Trigger hingegen kann zu Überraschungen führen, da die ROWCOUNT Restriktion auch für den Trigger gilt.

Es mag sinnvoll sein, zu überlegen, warum Sie dies machen wollen. Wenn Ihre Intention ist, den Output auf einer Webseite zu limitieren, ist es unter Umständen die bessere Strategie, grössere Mengen von Daten in Blöcken von 500 Zeilen auf einmal einzulesen, so dass Sie nicht immer wieder auf die Datenbank zugreifen müssen, wenn der Benutzer auf "Weiter" klickt.

create table @tbl
In diesem Fall gibt es kein Problem mit Berechtigungen (Ihre Benutzer brauchen die entsprechenden Berechtigungen zum Erstellen von Tabellen sowieso, egal ob es sich um ein dynamisches oder ein statisches Statement handelt) oder Caching. Und, falls es interessiert, auch kein Problem mit Abhängigkeiten. Von diesem Standpunkt aus gesehen, gibt es kein Argument gegen dynamisches SQL.

Eine Frage bleibt aber weiterhin gültig: Warum? Warum wollen Sie dies machen? Es mag vielleicht Sinn machen, in administrativen Skripten, in denen Sie eine Reihe von Tabellen erstellen müssen, die einander sehr ähnlich sind. Wenn Sie aber dynamisch "on-the-fly" Tabellen in Ihrer Applikation erstellen, haben Sie Grundsätzliches über Datenbankdesign verpasst. In einer relationalen Datenbank sollte die Menge der Tabellen und Spalten konstant bleiben. Diese mag sich zwar unter Umständen mit neueren Versionen ändern, aber nicht zur Laufzeit. Lesen Sie bitte die Diskussion select * from sales + @yymm.

Wenn manche Leute dies machen, scheint es, als ob Sie eindeutige Namen für temporäre Tabellen konstruieren wollen. Dies ist absolut unnötig, da dieses Feature bereits in SQL Server eingebaut ist. Wenn Sie sagen.

   CREATE TABLE #nisse (a int NOT NULL)

dann ist der aktuelle Name hinter den Kulissen tatsächlich manchmal wesentlich länger, und keine andere Verbindung ist in der Lage, die Instanz von #nisse auch nur zu sehen.

Möchten Sie eine permanente Tabelle erstellen, die einmalig für eine Verbindung ist, weil Sie vielleicht Disconnected Recordsets verwenden und keine temporäre Tabellen erstellen können, mag es vielleicht besser sein, eine Tabelle zu erstellen, die sich alle Clients teilen, wo aber die erste Spalte ein Schlüssel ist, der nur für diesen Client gilt.

Linked Servers
Dieses Thema ist zum Teil dem ähnlich, wo ein Datenbankname parametrisiert werden sollte. Die Antwort ist aber nicht die gleiche. Wenn es möglich ist, eine gespeicherte Prozedur auf dem linked Server zu installieren, können Sie den Namen der Prozedur auch dynamisch bilden:

   SET @sp = @server + 'db.dbo.some_sp'

   EXEC @ret = @sp @par1, @par2...

Wenn Sie eine lokale Tabelle mit einer remote Tabelle auf einem remote Server durch einen JOIN verbinden wollen, die gerade im Augenblick bestimmt werden soll, ist dynamisches SQL wahrscheinlich der beste Weg hierfür.

Zwar existiert auch hier eine Alternative, diese ist aber nur für manche Situation einsetzbar. Sie können sp_addlinkedserver einsetzen, um eine Art Alias für den Moment zu erstellen, wie dieses Codebeispiel zeigt:

   EXEC sp_addlinkedserver MYSRV, @srvproduct='Any',

                                  @provider='SQLOLEDB', @datasrc=@@SERVERNAME

   go

   CREATE PROCEDURE linksrv_demo_inner WITH RECOMPILE AS

      SELECT * FROM MYSRV.master.dbo.sysdatabases

   go

   EXEC sp_dropserver MYSRV

   go

   CREATE PROCEDURE linksrv_demo @server sysname AS

      IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'MYSRV')

         EXEC sp_dropserver MYSRV

      EXEC sp_addlinkedserver MYSRV, @srvproduct='Any',

                              @provider='SQLOLEDB', @datasrc=@server

      EXEC linksrv_demo_inner

      EXEC sp_dropserver MYSRV

   go

   EXEC linksrv_demo 'Server1'

   EXEC linksrv_demo 'Server2'

Hier werden zwei Prozeduren erstellt. Die innere Prozedur richtet den linked Server Alias MYSRV ein, der auf den Server verweist, mit dem wir kommunizieren wollen und löscht diesen Alias wieder, sobald wir fertig sind. Diese Prozedur enthält den tatsächlichen Zugriff auf den linked Server. Ich habe WITH RECOMPILE mit angegeben, um sicherzustellen, dass ich keinen veralteten Plan benutze, der auf einen anderen Server verweist.

Diese Methode ist nur sinnvoll unter bestimmten Voraussetzungen:

• Sie muss ausgeführt werden durch jemanden, der die Privilegien besitzt, einen linked Server einzurichten. Normalerweise sind dies die festen Serverrollen sysadmin und setupadmin. Alse scheiden einfache Benutzer aus.
• Da Sie eine serverweite Definition verändern, können Sie nicht mehrere Instanzen dieser Prozedur laufen lassen.

Beachten Sie: Bei Ihren Tests stellen Sie vielleicht fest, dass es auch ohne WITH RECOMPILE funktioniert. Sie kriegen es vielleicht auch hin, den Aufruf an sp_addlinkedserver in die gleiche Prozedur einzubauen wie die Referenz zu diesem Server. Ist der linked Server aber nicht definiert, wenn SQL Server versucht, einen Abfrageplan für die gespeicherte Prozedur zu erstellen, wird die ganze Prozedur scheitern.

OPENQUERY
Die Rowset Funktionen OPENQUERY und OPENROWSET rufen oft nach dynamischem SQL. Ihr zweites Argument ist ein SQL String und sie akzeptieren keine Variablen. Dies kann oft lästig sein, da Sie leicht dabei, drei Ebene von Quotierungen erhalten. Hier kann die Funktion quotestring(), die ich Ihnen weiter oben vorgestellt habe, sehr hilfreich sein:

   DECLARE @remotesql nvarchar(4000),

           @localsql  nvarchar(4000),

           @state     char(2)

 

   SELECT @state = 'CA'

   SELECT @remotesql = 'SELECT * FROM pubs.dbo.authors WHERE state = ' +

                        dbo.quotestring(@state)

   SELECT @localsql  = 'SELECT * FROM OPENQUERY(MYSRV, ' +

                        dbo.quotestring(@remotesql) + ')',

 

   PRINT @localsql

   EXEC (@localsql)

Die eingebaute Funktion quotename() ist gewöhnlich nicht hilfreich, da die SQL Statements die Grenze von 129 Zeichen für den Inputparameter überschreiten können.

Dynamische Spaltenbreite
Angenommen, dass Sie eine gespeicherte Prozedur schreiben, die Daten präsentieren soll, und dass die Oberfläche von der aus diese Prozedur gestartet werden soll, der Query Analyzer ist (vielleicht weil dies eine sysadmin Prozedur ist). Um die Ausgabe einfacher lesen zu können, möchten Sie die Spaltenbreite genauso weit haben, dass die Daten nicht abgeschnitten erscheinen, gleichzeitig wollen Sie aber auch keinen Platz verschwenden. Dies ist etwas, was Sie mit dynamischem SQL erreichen können. Da typischerweise temporäre Tabellen werden, gibt es hier kein Berechtigungsproblem.

Ich möchte Ihnen hier keine Beispiel zeigen, sondern Sie vielmehr auf den Code verweisen. Das Beispiel, welches Sie am schnellsten zur Hand haben, ist die beliebte (aber undokumentierte Systemprozedur sp_who2. Sie finden den Code durch die Eingabe von exec master..sp_helptext sp_who2, oder in dem Sie die Browser Fähigkeiten des Query Analyzers oder Enterprise Managers nutzen.

Ein weiteres Beispiel finden Sie auf meiner Website in der Prozedur aba_lockinfo.

Danksagung und Feedback

Ich möchte mich bei folgenden Personen bedanken, die wertvolle Vorschläge und Anregungen für diesen Artikel gegeben haben. SQL Server MVPs Tibor Karaszi, Keith Kratochvil, Steve Kass, Umachandar Jaychandran und Hal Berenson, sowie Pankul Verma, Anthony Faull, Karl Jones, Marcus Hansfeldt, Jeremy Lubich und Simon Hayes.

Ferner möchte ich ASP MVP Jongshin Kim danken, der eine koreanische Übersetzung dieses Artikels erstellt hat sowie Frank Kalis für seine deutsche Übersetzung und Simon Hayes für die Spanische.

Wenn Sie Verbesserungsvorschläge, oder Korrekturen bzgl. Inhalt, Sprache oder Formatierung haben, mailen Sie mir bitte an esquel@sommarskog.se. Falls Sie technische Fragen haben, die von vielen erfahrenen Personen beantwortet werden können, möchte ich Sie ermutigen, an eine der beiden folgenden Newsgroups zu posten: microsoft.public.sqlserver.programming oder comp.databases.ms-sqlserver.

Revisions Geschichte

17.04.2005 - Beispiel zu EXEC + sp_executesql hinzugefügt. Ebenso wie die Verwendung von nvarchar(max).

30.05.2004 - Spanische Übersetzung verfügbar

08.02.2004 - Deutsche Übersetzung verfügbar. Kleinere sprachliche Korrekturen

02.12.2003 - Koreanische übersetzung ist verfügbar. Das Beispiel zur Verwendung der cursor Variablen mit dynamischem SQL wurde hinzugefügt. Die Beschreibung des ersten Parameters zu sp_executesql wurde modifiziert.

Der Originalartikel steht hier