Freigeben über

  • Artikel

Entwerfen gespeicherter Prozeduren (Datenbankmodul)

Transact-SQL-Code, der als Batch geschrieben werden kann, kann fast immer zum Erstellen einer gespeicherten Prozedur verwendet werden.

Regeln für das Entwerfen gespeicherter Prozeduren

Für den Entwurf gespeicherter Prozeduren gelten u. a. die folgenden Regeln:

  • Die CREATE PROCEDURE-Definition selbst kann SQL-Anweisungen in beliebiger Anzahl und eines beliebigen Typs enthalten; hiervon ausgenommen sind die folgenden Anweisungen. Diese können niemals innerhalb einer gespeicherten Prozedur verwendet werden.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE oder ALTER FUNCTION

    CREATE oder ALTER TRIGGER

    CREATE oder ALTER PROCEDURE

    CREATE oder ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • Andere Datenbankobjekte können innerhalb einer gespeicherten Prozedur erstellt werden. Sie können auf ein Objekt verweisen, das in derselben gespeicherten Prozedur erstellt wurde, vorausgesetzt, es wurde erstellt, bevor der Verweis erfolgt.

  • Sie können auf temporäre Tabellen innerhalb einer gespeicherten Prozedur verweisen.

  • Wenn Sie eine lokale temporäre Tabelle innerhalb einer gespeicherten Prozedur erstellen, kann die temporäre Tabelle nur im Rahmen der Aktionen eingesetzt werden, die von der gespeicherten Prozedur ausgeführt werden; nach Beendigung der gespeicherten Prozedur ist die Tabelle nicht mehr vorhanden.

  • Wenn Sie eine gespeicherte Prozedur ausführen, die eine andere gespeicherte Prozedur aufruft, kann die aufgerufene gespeicherte Prozedur auf alle von der ersten gespeicherten Prozedur erstellten Objekte zugreifen, einschließlich temporärer Tabellen.

  • Wenn Sie eine remote gespeicherte Prozedur ausführen, die Änderungen an einer Remoteinstanz von Microsoft SQL Server vornimmt, kann für diese Änderungen kein Rollback ausgeführt werden.Remote gespeicherte Prozeduren nehmen nicht an Transaktionen teil.

  • Die maximale Anzahl der Parameter in einer gespeicherten Prozedur beträgt 2100.

  • Die maximale Anzahl lokaler Variablen in einer gespeicherten Prozedur ist nur durch den verfügbaren Arbeitsspeicher begrenzt.

  • Die maximale Größe einer gespeicherten Prozedur beträgt 128 Megabyte (MB) und hängt vom verfügbaren Arbeitsspeicher ab.

Kennzeichnen von Namen innerhalb von gespeicherten Prozeduren

Den Objektnamen innerhalb einer gespeicherten Prozedur, die mit Anweisungen verwendet werden (z. B. SELECT oder INSERT) und die nicht im Schema qualifiziert sind, wird standardmäßig das Schema der gespeicherten Prozedur zugeordnet. Wenn ein Benutzer, der eine gespeicherte Prozedur erstellt, die Namen der Tabellen oder Sichten nicht qualifiziert, auf die in einer der SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen innerhalb der gespeicherten Prozedur verwiesen wird, wird der Zugriff auf diese Tabellen, der über die gespeicherte Prozedur erfolgt, standardmäßig auf den Benutzer beschränkt, der die Prozedur erstellt hat.

Objektnamen, die mit DDL-Anweisungen (Data Definition Language) verwendet werden, z. B. mit den CREATE-, ALTER-, DROP-, DBCC- oder EXECUTE-Anweisungen bzw. mit dynamischen SQL-Anweisungen, müssen mit dem Namen des Objektschemas qualifiziert werden, wenn andere Benutzer die gespeicherte Prozedur verwenden sollen. Das Angeben des Schemanamens für diese Objekte stellt sicher, dass der Name unabhängig vom Aufrufer der gespeicherten Prozedur in das gleiche Objekt aufgelöst wird. Wenn kein Schemaname angegeben wird, versucht SQL Server, den Objektnamen zuerst mithilfe des Standardschemas des Aufrufers oder des Benutzers aufzulösen, der in der EXECUTE AS-Klausel angegeben wurde. Dann wird das dbo-Schema verwendet.

Verbergen von Prozedurdefinitionen

Um den Originaltext der CREATE PROCEDURE-Anweisung in ein verborgenes Format zu konvertieren, verwenden Sie die WITH ENCRYPTION-Option. Die Ausgabe der Verbergung ist in keiner der Systemtabellen oder Sichten in SQL Server 2008 direkt sichtbar: Benutzer ohne Zugriff auf Systemtabellen, Systemsichten oder Datenbankdateien können den verborgenen Text nicht abrufen. Der Text ist für entsprechend privilegierte Benutzer mit direktem Zugriff auf Datenbankdateien jedoch verfügbar. Diese Benutzer können die Verbergung möglicherweise zurückentwickeln, um den Originaltext der gespeicherten Prozedurdefinition zu erhalten.

Optionen der SET-Anweisung

Database Engine (Datenbankmodul) speichert die Einstellungen sowohl für SET QUOTED_IDENTIFIER als auch für SET ANSI_NULLS, wenn eine gespeicherte Transact-SQL-Prozedur erstellt oder geändert wird. Diese ursprünglichen Einstellungen werden verwendet, wenn die gespeicherte Prozedur ausgeführt wird. Deshalb werden alle Clientsitzungseinstellungen für SET QUOTED_IDENTIFIER und SET ANSI_NULLS während der Ausführung der gespeicherten Prozedur ignoriert. SET QUOTED_IDENTIFIER- und SET ANSI_NULLS-Anweisungen, die in der gespeicherten Prozedur auftreten, beeinflussen nicht die Funktionalität der gespeicherten Prozedur.

Andere SET-Optionen, wie z. B. SET ARITHABORT, SET ANSI_WARNINGS oder SET ANSI_PADDINGS, werden nicht gespeichert, wenn eine gespeicherte Prozedur erstellt oder geändert wird. Wenn die Logik der gespeicherten Prozedur von einer bestimmten Einstellung abhängig ist, schließen Sie eine SET-Anweisung am Anfang der Prozedur ein, um die richtige Einstellung sicherzustellen. Wenn eine SET-Anweisung aus einer gespeicherten Prozedur heraus ausgeführt wird, bleibt die betreffende Einstellung nur so lange in Kraft, bis die gespeicherte Prozedur abgeschlossen ist. Die Einstellung wird dann mit dem Wert wiederhergestellt, den sie hatte, als die gespeicherte Prozedur aufgerufen wurde. Dies gibt einzelnen Clients die Möglichkeit, die gewünschten Optionen festzulegen, ohne die Logik der gespeicherten Prozedur zu beeinflussen.

HinweisHinweis

ANSI_WARNINGS wird beim Übergeben von Parametern in einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batchanweisung nicht berücksichtigt. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die Anweisung INSERT oder UPDATE wird erfolgreich ausgeführt.