Start

Unternehmen

Buch-Katalog

Seminare

Leserservice

Comelio-Blog

Datenbanken SQL MS SQL Server Programmierbarkeit Funktionen Verwaltungsarbeiten bei Modulen Programmierung mit T-SQL XML in T-SQL zerlegen Variablen mit XML-Inhalt XML mit XPath zerlegen Datentypmethoden für XML-Bearbeitung XPath Definition von Webservices XSLT Technologien von Webservices Webservices im MS SQL Server SOAP-Webservices Nachrichtenstruktur von Webservices Batch-Einsatz bei Webservices Oracle PHP UML C#.NET XML Schema XSLT

ERP / PPS / Prozesse

Business Intelligence

Server-Technologien

Software-Technologien

Technologie-Beratung

Individual-Software

Produkte

Comelio GmbH
Rellinghauser Straße 10
D-45128 Essen
Deutschland
Fon: 0201-437517-0
Fax: 0201-437517-10
info@comelio.com

Comelio GmbH
Goethestraße 34
D-13086 Berlin
Deutschland
info@comelio.com

Comelio GmbH (Ecos)
Glockengießerwall 17
D-20095 Hamburg
Deutschland
info@comelio.com

Comelio GmbH (Ecos)
Mainzer Landstraße 27-31
D-60329 Frankfurt
Deutschland
info@comelio.com

Comelio GmbH (Ecos)
Stiglmaierplatz/Dachauer Str. 37
D-80335 München
Deutschland
info@comelio.com

Comelio GmbH (Ecos)
Liebknechtstr. 33
D-70565 Stuttgart
Deutschland
info@comelio.com

Comelio GmbH
Nevinghoff 16
D-48147 Münster
Deutschland

Comelio GmbH
Friedrich - List - Platz 1
D-04103 Leipzig
Deutschland

Comelio GmbH
St. Johanner Strasse 41-43
D-66111 Saarbrücken
Deutschland

Comelio GmbH
Kaiser-Wilhem-Ring 27–29
D-50672 Köln
Deutschland

Comelio GmbH
Münsterstraße 248
D-40470 Düsseldorf
Deutschland

Comelio GmbH
Fürther Strasse
D-90429 Nürnberg
Deutschland

Comelio GmbH

Bremen
Deutschland

Comelio-Blog > MS SQL Server > Programmierbarkeit

Programmierbarkeit

Unter dem schönen, neuen Wort ‚Programmierbarkeit’ verbirgt sich im MS SQL Server 2005 die Möglichkeit, Prozeduren, Funktionen und Trigger als T-SQL- und .NET-Module zu hinterzulegen, welcher der Datenbank eine zusätzliche Schicht hinzufügen. Diese enthält in Form der genannten Module bereits einen Teil der Software, welcher die Datenbank erneut von der eigentlichen Software in bspw. .NET verbirgt. Neben dem Effekt des Verbergens und Schützens ist diese Technik auch deswegen so interessant, weil durch sie eine vereinfachte Benutzung der Datenbank gelingt.

Kontakt Anrede* Herr Frau Vorname* Nachname* Firma E-Mail* Tel-Nr. Bereich* Comelio-Blog > MS SQL Server > Programmierbarkeit Freitext

Programmierbarkeit

Ein typisches Beispiel ist in diesem Zusammenhang die Entwicklung einer Prozedur, welche für eine ganze Reihe an Parametern einen Einfügevorgang in eine Tabelle vereinfacht. Die äußere Software und damit auch der Programmierer ist nicht gezwungen, entsprechende INSERT-Anweisungen zu formulieren oder daran zu denken, welche Parameter für einen sinnvollen Datensatz notwendig sind. Stattdessen ruft er nur noch diese Prozedur auf, was darüber hinaus auch noch in verschiedenen Programmiersprachen gelingt.

Dieses Kapitel stellt die beiden Themen Funktionen und Prozeduren vor, während Trigger dem Buch zur MS SQL Server-Administration und die Programmierbarkeit von .NET einem entsprechenden .NET-Buch vorbehalten sind. Insbesondere die Verwendung von .NET ist zwar sicherlich als Fähigkeit extrem interessant, allerdings nützt ein solches Kapitel für Leser nichts, die zunächst noch .NET an sich lernen müssten.

Prozeduren

Die Erstellung von Prozeduren und Funktionen ist grundsätzlich in allen kommerziellen Großdatenbanken wie MS SQL Server oder Oracle und mittlerweile auch in Open Source-Produkten wie MySQL möglich. Während Oracle hier entweder die eigene Programmiersprache PL/SQL (eine sehr stark erweiterte Form von T-SQL) sowie auch die Verwendung von Java erlaubt, bietet hier der MS SQL Server entweder T-SQL oder .NET an. Dabei entspricht eine Prozedur einem Konstrukt, das unter vielen verschiedenen Namen wie Modul, DB-Routine, Unterprogramm oder - wie es Microsoft selbst vorschlägt - als Auflistung von T-SQL-Anweisungen, die unter einem Namen in der DB gespeichert ist. Dabei ermöglicht es eine Prozedur, nicht nur wie ein Makro mehrere Anweisungen der Reihe nach auszuführen und für einen späteren wiederholten Aufruf verfügbar zu machen, sondern bietet darüber hinaus auch die Fähigkeit, Parameter einer äußeren Anwendung entgegen zu nehmen und diese zu verarbeiten. Prozeduren erscheinen im Normalfall als permanent in der Datenbank gespeicherte Objekte, können allerdings auch wie Tabellen als temporär für eine Sitzung und global temporär für alle Sitzungen verfügbar gemacht werden.

Die Anweisung in T-SQL, um eine Prozedur zu erstellen, ist sehr ähnlich zur Funktionserstellung. Die Unterschiede ergeben sich in den Details und in den Einsatzbereichen.

Die nachfolgende Abbildung zeigt die verschieden Arten von Prozeduren, die im MS SQL Server erstellt werden können. Auf der unteren Hälfte der Abbildung befinden sich sechs beispielhaft angegebene Tabellen, die zu zwei verschiedenen Bereichen zusammen gefasst werden. Zwischen einer Anwendung wie das schon leidlich bekannte Management Studio und einer anderen, individuell erstellten Anwendung liegt eine Schicht von Prozeduren. Sie sollen bspw. den Abruf und die Manipulation von Daten beeinflussen. Gründe können hier erhöhte Sicherheit, erweiterte Möglichkeit zur Validierung oder auch vereinfachte Benutzbarkeit von außen sein.

Auf der linken Seite der drei unterschiedlichen Prozedurarten werden die drei DML-Befehle INSERT, UPDATE und DELETE aufgelistet. Sie sollen zeigen, dass Prozeduren zum Einsatz kommen können, wenn Datenmanipulation bspw. für komplexe Datenstrukturen durch das Angebot einer zentralen Zugriffsschicht von Prozeduren vereinfacht werden sollen.

Direkt rechts neben diesen Prozeduren gibt es eine weitere Gruppe. Sie wirkt sich notwendigerweise auf Tabellen aus, sofern diese Tabellen nicht Administrationsdaten speichern. Stattdessen führen sie wie Skripte in einem Server häufig wiederkehrende Aufgaben aus, die von einem Administrator ansonsten durch im Dateisystem gespeicherte Skripte oder Eingaben in der grafischen Benutzerschnittstellen durchgeführt werden müssten. Solche Prozeduren bzw. das entsprechende SQL wie bspw. Massendaten-Import/-Export oder allgemeine Verwaltungsaufgaben werden im Buch zur Administration beschrieben. Diese und die DML-Prozeduren haben die Möglichkeit, Rückgabewerte zurückzuliefern. Diese können per Referenz aus der Parameterliste abgerufen werden. Dies bedeutet, dass Prozeduren nicht wie Funktionen auf der rechten Seite einer Zuweisung stehen können, sondern als eigenständige Anweisung. Dies bedeutet allerdings auch, dass Prozeduren mehrere Rückgabewerte zurückliefern können.

Auf der rechten Seite schließlich wird eine Besonderheit im MS SQL Server dargestellt, welche insbesondere für Umsteiger von Oracle überraschend sein kann. Eine Prozedur bietet die Möglichkeit, relationale Ergebnismengen und Cursor zurückzuliefern. Insbesondere die erste Technik ist besonders interessant, da im Gegensatz zu Sichten hier die Möglichkeit besteht, vorgefertigte Filter über die Prozedurparameter anzubieten.

Es ließe sich auch noch eine andere Ordnung für Prozeduren finden: Man kann zwischen temporären und tatsächlich gespeicherten („normalen“) Prozeduren unterscheiden, wobei innerhalb der temporären wiederum zwischen globalen und nur auf eine Sitzung bezogenen Prozeduren unterschieden werden kann. Man kann allerdings auch noch die Gruppe der so genannten erweiterten Prozeduren zählen, die früher mit C und der ODS API (ähnlich wie in C-geschriebene MySQL-Prozeduren) erstellt werden konnten, und zu denen man heute die .NET-Prozeduren zählen müsste, wenn der Begriff in der 2005-Literatur nicht weitestgehend verschwunden wäre. Man kann dann auch noch zwischen benutzerdefinierten Prozeduren und Systemprozeduren unterscheiden, wobei die eine Gruppe in diesem Kapitel besonders interessiert, weil sie über den CREATE-Befehl vom Benutzer erstellt werden können, während die anderen Prozeduren bereits wie SQL-Funktionen in der Datenbank vorhanden sind. In diesem Zusammenhang muss erwähnt werden, dass dies Prozeduren sind, deren Namen mit sp_ beginnt und die in der Master-Datenbank gespeichert werden. Diese Prozeduren können (sollten aber nicht) auch selbst erstellt werden, wobei diese dann wie ein Chamäleon arbeiten und dafür sorgen, dass auf den aktuellen DB-Kontext bezogene Anweisungen (und sei es nur der Abruf des DB-Namens wie DB_NAME()) auf die Datenbank bezogen werden, in der man sich gerade befindet, obwohl die Prozedur aus der Master-DB abgerufen wird. Schließlich gelangt man innerhalb von diesem sich abzeichnendem Baum zu der Unterscheidung, die gerade getroffen wurde, die für die Zwecke dieses Kapitels eigentlich am besten geeignet ist.

Eine Prozedur wird im Standardschema des Benutzers gespeichert, sofern nicht ausdrücklich ein anderes Schema angegeben ist. Sie wird nur in der aktuellen Datenbank gespeichert. Lediglich temporäre Prozeduren speichert man automatisch in der tempdb. Um den Quelltext einer Prozedur zu sichern, kann man ebenfalls ein .sql-Skritp erstellen, wobei man allerdings darauf achten muss, dass vorherige Anweisungen mit GO von der Anweisung CREATE PROCEDURE getrennt sind.

Nachfolgend ist die allgemeine Syntax angegeben:

CREATE { PROC | PROCEDURE } 
       [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ [ OUT [ PUT ] 
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

Die verschiedenen Argumente, von denen sich verschiedene bereits anhand ihres Namens verstehen lassen, sind nachfolgend aufgelistet:

• schema_name enthält den Namen des Schemas, in dem die Prozedur gespeichert wird.
• procedure_name enthält den Namen der Prozedur, der den allgemeinen Benennungsregeln entspricht und im Schema eindeutig sein muss. Das Präfix sp_ ist dabei am besten zu vermeiden, da es für gespeicherten Systemprozeduren bereits verwendet wird. Ob eine Prozedur lokal oder global temporär erstellt wird, kann man wie bei Tabellen über ein (temporär nur für die aktuelle Sitzung) oder zwei Rautenzeichen (##, global temporär für alle Sitzungen) zu Beginn des Namens festlegen. Insgesamt ist der Name auf 128 Zeichen und der Name einer lokalen temporären Prozedur inkl. dem Rautenzeichen auf 118 Zeichen begrenzt.
• ; number enthält eine optionale Zahl. Sie erlaubt es, mehrere Prozeduren unter dem gleichen Namen in einem Schema anzugeben. Diese unterscheiden sich in der Nummer, werden allerdings durch den gemeinsamen Namen gruppiert. Die Zahl kann auch für den Löschvorgang aller Prozeduren der Gruppe genutzt werden. Bsp.: meineProzedur;1, meineProzedur;2. Diese Eigenschaft ist auf das Abstellgleis geschoben und wird irgendwann aus der Datenbank entfernt.
• @parameter enthält einen Parameter der Prozedur, von dem es maximal 2100 geben darf. Sofern kein Standardwert oder ein berechneter Bezug auf einen anderen Parameter angegeben ist, ist es notwendig, beim Aufruf der Prozedur einen Wert für diesen Parameter bereitzustellen. Hier muss der Namen auch den allgemeinen Bezeichnungsregeln entsprechen und wird - ähnlich einer Variable - mit Datentyp und Standardwert angegeben.
• [ type_schema_name. ] data_type enthält den Datentyp eines Parameters sowie sein optionales Schema. Alle Datentypen außer table können hier eingesetzt werden. Der Datentyp cursor kann nur für einen Ausgabeparameter eingesetzt werden. Die Prozedur liefert so einen Cursor zurück.
• VARYING kann nur für den Datentyp cursor verwendet werden und gibt an, dass die Struktur der relationalen Ergebnismenge, die zurückgeliefert wird, variieren kann.
• default enthält einen möglichen Standardwert für den Parameter. Dies ist eine Konstante oder auch NULL.
• OUTPUT legt fest, dass dieser Parameter einen Rückgabewert an die aufrufende Anweisung liefert, welcher per Referenz übertragen wird. Auch text-, ntext- und image-Parameter können verwendet werden, solange es keine .NET-Prozedur ist.
• RECOMPILE legt für reine T-SQL-Prozeduren fest, dass das Datenbankmodul den Ausführungsplan dieser Prozedur nicht zwischenspeichert, sondern zur Laufzeit kompiliert.
• ENCRYPTION legt reine T-SQL-Prozeduren fest, sodass der Quelltext, in der die Prozedur erstellt wurde, chiffriert wird und damit nur für Benutzer mit sehr weiten Rechten den Text abrufen können.
• EXECUTE AS legt fest, unter welchem Berechtigungskontext die Prozedur ausgeführt werden darf. Dabei gilt die allgemeine Syntax { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }. Dieses Thema wird später noch ausführlich behandelt.
• FOR REPLICATION legt fest, dass diese Prozedur nur im Rahmen von Replikationen und damit DB-Sicherungsvorgängen ausgeführt wird. Sie darf keine Parameter enthalten, ist nicht für .NET-Prozeduren zulässig, die RECOMPILE-Option ist untersagt, und sie wird nicht auf dem Abonennten einer Replikation durchgeführt.
• <sql_statement> enthält die T-SQL-Anweisungen und damit den eigentlichen Programmtext der Prozedur.
• EXTERNAL NAME , assembly_name.class_name.method_name gibt die statische Methode einer .NET-Assembly (dll) an, die als Prozedur verfügbar gemacht werden soll.

Um eine Prozedur zu ändern, ist anstelle der CREATE-Anweisung nur das Schlüsselwort ALTER anzugeben. Dieses sorgt dafür, dass die vorhandene Prozedur mit dem nun angegebenen Quelltext überschrieben wird.

ALTER { PROC | PROCEDURE } 
      [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
    [ VARYING ] [ = default ] [ [ OUT [ PUT ] 
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS 
     { <sql_statement> [ ...n ] | <method_specifier> }

Aus der Datenbank kann der Quelltext zur Überarbeitung aus dem Kontextmenü einer gespeicherten Prozedur abgerufen werden. Dazu wählt man SKRIPT FÜR GESPEICHERTEN PROZEDUREN ALS / ALTER IN und dann eine der drei Optionen, um den Quelltext in die Zwischenablage zu kopieren, einer Datei zu speichern oder sofort in einem neuen Abfragefenster zu öffnen.

Um eine Prozedur zu löschen, ist wie sonst auch bei Schema-Objekten die

  DROP-Anweisung einzusetzen.

  DROP { PROC | PROCEDURE } 

  { [ schema_name. ] procedure } [ ,...n ]

Prozedurarten

Die verschiedenen Arten von Prozeduren sind bereits zuvor kurz in einer Abbildung vorgestellt worden. Sie sollen nun noch einmal jeweils mit einem typischen Beispiel unterlegt werden.

Rückgabe einer Ergebnismenge

Eine wesentliche Eigenschaft des MS SQL Servers ist es, Prozeduren erstellen zu können, die eine relationale Ergebnismenge zurückliefern können. Dies bietet eine Sicht selbstverständlich ebenfalls an. Hier allerdings ist es notwendig, eine WHERE-Klausel selbst zu schreiben, die einen zusätzlichen Filter auf die Daten anwendet. Bei der Verwendung einer Prozedur dagegen kann man eine Reihe von Parametern vorgeben, welche typische Filter bzw. gewünschte Filterangaben (aus Sicherheits-, Validierungs- oder Bequemlichkeitsgründen) bereits vorgeben. Dies wird im nächsten Beispiel gezeigt.

Die Prozedur liefert Produktinformationen anhand der Produktnummer. Kein anderer Filter ist notwendig, um an Produktdaten zu gelangen. Auch muss man die Ergebnismenge nicht so gut kennen, um einen eigenen Filter auf eine bestimmte Untermenge an Spalten anzuwenden. Lediglich die spätere Übergabe eines geeigneten Wertes ist notwendig. Die Prozedur legt man mit der CREATE-Anweisung an und hängt dann die Parameterliste an ihren Namen an. Als Parameter übergibt man eine Produktnummer, die verpflichtend ist und die auch keinen Standardwert besitzt. Mit Hilfe dieser Produktnummer sucht man in einer einfachen Abfrage verschiedene Spaltenwerte aus der Product-Tabelle heraus. Die Prozedur endet auch mit dieser Abrage, sodass sie insgesamt genau dieses relationale Ergebnis zurückliefert.

-- Existenzprüfung und Erstellung einer Pozedur
IF OBJECT_ID ( 'Production.usp_GetProductByNumber', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.usp_GetProductByNumber;
GO
CREATE PROCEDURE Production.usp_GetProductByNumber (@vProductNumber nvarchar(25))
AS BEGIN
SELECT Name,Color, ListPrice, Size
  FROM Production.Product
 WHERE ProductNumber = @vProductNumber
END
GO
EXEC Production.usp_GetProductByNumber 'SO-B909-L'

Der vorherige Quelltext zeigte neben der einfachen Prozedur auch noch, wie man sie innerhalb von T-SQL aufrufen kann. Unter Angabe des Parameterwerts für ein beliebiges Paar Socken erhält man das Ergebnis genau so zurück, als hätte man eine Tabelle oder Sicht befragt.

Dies war ein Beispiel zu den Prozeduren, die mit einer SELECT-Anweisung enden und daher insgesamt nicht einen Rückgabewert zurückliefern, sondern eine Abfrage ausführen. Hierbei gibt es grundsätzlich keine Beschränkungen, was den Quelltext anbetrifft, der vor der letzten Abfrage steht und welcher damit quasi das Ergebnis einleitet. Man könnte sich vorstellen, dass man eine Art Produktsuchmaschine mit Hilfe einer Prozedur realisiert, die ansonsten nur in einer äußeren Anwendung denkbar wäre. Unter Angabe eines variierenden Datentyps oder ganz einfacher Zeichenkette kann man verschiedene Spalten in der Product-Tabelle untersuchen und die passenden Ergebnisse in eine table-Variable speichern, welche schließlich abgefragt wird. In der ersten Abfrage könnte man die Ergebnisse beschaffen, indem der Suchbegriff in der Produktnummer gesucht wird. Die zweite Abfrage, welche die table-Variable auffüllt, könnte im Namen suchen. Wenn schließlich alle relevanten Spalten untersucht sind, die man in diese Suchmaschine aufnehmen möchte, fragt man die table-Variable ab und liefert so das gesamte Ergebnis.

In der nachfolgenden Abbildung ist dieses Prinzip noch einmal dargestellt. In der unteren Hälfte befindet sich die zu Grunde liegende Tabelle in Form ihrer Daten, deren Struktur sehr viel umfassender ist, als es die Rückgabedaten der Prozedur vermuten lassen. Es ist also möglicherweise für die Anwendung, welche diese Daten über die Prozedur anspricht, gar nicht notwendig bzw. für den Benutzer möglicherweise sogar untersagt, mehr über die Daten und damit sowohl Struktur als auch Inhalte zu erfahren. Die Prozedur fragt in ihrer Eigenschaft als eigenes Schema-Objekt anstelle einer direkten Abfrage, die in der äußeren Anwendung abgesetzt wird, die Daten ab und verhindert so einen allzu großzügigen Blick auf diese Daten.

Sonstige Prozeduren

Neben den zuvor erläuterten Prozeduren gibt es noch diejenigen, welche nicht die Abfragen, sondern die Datenmanipulation steuern und vereinfachen sollen. Die nachfolgende Prozedur erleichtert es bspw., ein neues Produkt einzufügen. Man könnte sich hier noch vorstellen, dass der erzeugte Primärschlüsselwert zurückgeliefert wird. Dazu setzt man die OUTPUT-Klausel ein, was in einem späteren Beispiel vorgeführt wird. Die Prozedur erwartet diejenigen Werte für ein Produkt, die man in jedem Fall benötigt und könnte auch noch mit NULL ausgestattete weitere Parameter erwarten, die in der Tabelle nicht verpflichtend sind.

Solche Prozeduren sind überaus interessant, um eine vereinfachte Datenschnittstelle für Aktualisierungs- und Einfügevorgänge anzubieten. Es könnte sein, dass für eine Anwendung nicht alle Spalten relevant sind und daher nach außen nur eine begrenzte Spaltenauswahl angeboten werden soll. Es könnte genauso gut sein, dass besondere Berechnungen oder Validierungen stattfinden sollen, die nicht mit Hilfe einer CHECK-Bedingung realisiert werden können oder sollen. Sofern keine Trigger als automatisch ausgeführte Prozedur, die an eine Tabelle gebunden ist, zum Einsatz kommt, bietet sich eine solche Prozedur besonders an, da hier der Aspekt des vereinfachten Zugriffs zusätzlich realisiert werden kann.

Das nachfolgende Beispiel zeigt nur, wie überhaupt die schon in Kapitel 4 erstellte Product3-Tabelle, die im Download-Listing ebenfalls enthalten ist und zuvor angelegt wird, gefüllt wird. Sämtliche andere T-SQL-Anweisungen sind nicht spezifisch für Prozeduren, sondern können mit der bisher gezeigten Syntax umgesetzt werden.

CREATE PROCEDURE Production.usp_InsertProduct (
 @vName          varchar(30),
 @vProductNumber nvarchar(25),
 @vListPrice     money,
 @vStandardCost  money
)
AS BEGIN
 INSERT INTO Production.Product3 
 (Name, ProductNumber, ListPrice, StandardCost)
 VALUES (@vName, @vProductNumber, @vListPrice, @vStandardCost)
END

Parameter und Aufruf

Wie schon zuvor gezeigt, gibt es wenigstens zwei verschiedene Varianten, eine Prozedur aufzurufen und ihr Parameterwerte zu übergeben. Die eine Variante bezeichnet man als Positionsnotation, da hier die Position/Reihenfolge der Parameterwerte über ihre Zuordnung zu Prozedurparametern entscheidet. Die andere Variante bezeichnet man dagegen als Namensnotation, da in diesem Fall nur der Name über die Zuordnung entscheidet und hier also auch eine andere Reihenfolge genutzt werden kann. Insbesondere diese Namensnotation ermöglicht es auch, einfach Standardwerte aufzurufen, ohne die Parameter eigens anzusprechen.

Der Aufruf einer Funktion oder Prozedur erfolgt mit der EXEC-Anweisung, welche folgende allgemeine Syntax besitzt:

[{ EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH RECOMPILE ]
    }

Folgende Parameter kommen zum Einsatz:

• @return_status erwartet eine Ganzzahl mit dem so genannten Rückgabestatus eines Moduls. Bei einer Skalarfunktion ist dies der gewöhnliche Rückgabewert, welcher in jedem beliebigen Datentyp abgerufen werden kann
• module_name erwartet den (voll qualifizierten) Namen von Prozedur oder Skalarwertfunktion
• ; number erwartet die nicht mehr empfohlene Nummer eines Moduls, das in einer Gruppe von gleich benannten, sich aber durch die Nummer unterscheidenden Prozeduren erstellt wurde.
• @module_name_var erwartet eine Variable, welche den Namen des auszuführenden Moduls für sehr dynamische Modulauswahl und -angabe enthält.
• @parameter erwartet den Namen eines Parameters mit voran gestelltem @-Zeichen. Sofern der Namen ausdrücklich angegeben wird, muss die Reihenfolge der Parameter von Aufruf und Deklaration nicht übereinstimmen (Namensnotation)
• value erwartet den zugewiesenen Wert des Parameters. Sofern nur der Wert angegeben wird, ist die Reihenfolge der Parameter in Aufruf und Deklaration einzuhalten (Positionsnotation)
• @variable enthält eine Variable, die den Wert eines Parameters oder Ausgabeparameters speichert.
• OUTPUT gibt an, dass dieser Parameter ein Ausgabeparameter ist und Werte per Referenz zurückgibt. Innerhalb des Moduls ist derselbe Parameter ebenfalls mit OUTPUT angegeben.
• DEFAULT gibt an, dass der Standardwert verwendet werden soll.
• WITH RECOMPILE lässt einen neuen Ausführungsplan kompilieren, der nach der Ausführung wieder gelöscht wird. Dies ist zu verwenden, wenn die Parameterwerte sehr unterschiedlich sind und die Daten in großem Maße geändert werden.

Standardwerte

Wie auch bei Tabellen und ihren Spalten ist es bei Prozeduren ebenfalls erlaubt, Standardwerte anzugeben. Sie werden dann verwendet, wenn sie keine tatsächlichen Parameterwerte beim Aufruf überschreiben. Das nachfolgende Beispiel zeigt, wie ein solcher Standardwerte angegeben wird. Die Syntax erinnert sehr an eine Variablendeklaration, bei der die SET-Anweisung ausgelassen wurde. Inhaltlich sucht diese Prozedur wiederum Produkte heraus, wobei allerdings nicht die Produktnummer anzugeben ist, sondern stattdessen der minimale Listenpreis, die Farbe und die Produktkategorie. Diese Kategorie wird nun auf den Standardwert 1 gesetzt, so wie man sich auch noch vorstellen könnte, den Minimalpreis auf 0 zu setzen.

CREATE PROCEDURE Production.usp_GetProduct (
  @vListPrice money,
  @vColor     nvarchar(15),
  @vCategory  int = 1)
AS BEGIN
SELECT Name, Color, ListPrice, Size, ProductSubcategoryID
  FROM Production.Product
 WHERE Color = @vColor
   AND ListPrice > @vListPrice
   AND ProductSubcategoryID = @vCategory
END

Rückgabewert

Im Normalfall kommen Funktionen zum Einsatz, wenn Rückgabewerte benötigt werden. Dabei steht der Funktionsaufruf auf der rechten Seite einer Zuweisung bzw. erscheint als Ausdruck und liefert so diesen Rückgabewert direkt an eine Variable oder das diesen Ausdruck verarbeitende Ziel zurück. Eine Prozedur hingegen kann als eigenständige Anweisung fungieren, sodass es zunächst kein Ziel von Rückgabewerten gibt. Dies ist allerdings durch die Wertübergabe per Referenz und einen Parameter, der zusätzlich mit dem Schlüsselwort OUTPUT ausgezeichnet wurde, möglich. Bei einer Prozedur gibt es in diesem Zusammenhang auch keine Einschränkung bei der Anzahl an Rückgabewerten.

Das nachfolgende Beispiel zeigt zum einen, wie ein solcher Rückgabewerte eingerichtet, mit einem Wert gefüllt auch in der äußeren Anwendung (in diesem Fall T-SQL) abgerufen wird. Die Prozedur soll wieder Produktinformationen liefern, wobei diese allerdings in Form einer zusammenfassenden Zeichenkette zurückgeliefert werden sollen. Dazu gibt es den dritten Parameter, der mit OUTPUT ausgezeichnet wurde. Die anderen beiden Parameter bilden wie zuvor den Minimalpreis und die Farbe ab. Eigentlich müsste man einen Cursor erstellen, um alle Produktinformationen zusammen abzurufen und bspw. als HTML-Text aufzubereiten, doch um das Beispiel zu verkürzen werden nur die Werte des ersten abgerufenen Datensatzes abgerufen und im Rückgabeparameter zusammen gesetzt.

CREATE PROCEDURE Production.usp_GetProduct (
  @vListPrice money,
  @vColor     nvarchar(15),
  @vSummary   nvarchar(255) OUTPUT)
AS BEGIN
-- Variablen deklarieren
DECLARE @vName nvarchar(50),
        @vDetails nvarchar(505)
-- Daten abrufen
SELECT @vName = Name,
       @vDetails = Color + ' ' + STR(ListPrice)
  FROM Production.Product
 WHERE Color = @vColor
-- Output-Parameter setzen
SET @vSummary = @vName + ' ' + @vDetails
END

Um einen solchen Rückgabewert abzurufen, benötigt man außen zunächst eine entsprechende Variable, die einen für den Rückgabewert geeigneten Datentyp besitzt. Diese Variable wird bei der Positionsnotation einfach ebenfalls mit dem Schlüsselwort OUTPUT an die Stelle in der Parameterliste gesetzt, an der ein Rückgabeparameter erwartet wird. Bei der Namensnotation dagegen ist die Position grundsätzlich egal, weil vor Nennung der Variablen auch noch der Parametername angegeben werden muss. Das Schlüsselwort OUTPUT bleibt bestehen. Nach dem Aufruf ist die zuvor deklarierte und mit keinem Wert versehene Variable gefüllt. Ein möglicher vorhandener Wert wäre überschrieben worden.

-- Aufruf mit Output-Parametern
DECLARE @vProductText nvarchar(255)
EXEC Production.usp_GetProduct 300.00, 'Black',
                               @vProductText OUTPUT
PRINT @vProductText
-- Aufruf mit Namensnotation
EXEC Production.usp_GetProduct  @vColor = 'Blue',
                              @vListPrice = 220.00,
                              @vSummary = @vProductText OUTPUT
PRINT @vProductText

Neukompilierung

Wie schon in der allgemeinen Syntax erwähnt, bestimmt die Option WITH RECOMPILE, dass das Datenbankmodul den erzeugten Abfrageplan, d.h. die vordefinierte und normalerweise die Geschwindigkeit positiv beeinflussende Art und Weise der (wiederholten) Ausführung zu verwerfen. Dies zwingt den Abfrageoptimierer, bei jeder Abfrage einen neuen Plan zu kompilieren, da dieser nicht zwischengespeichert wird.

Sofern gar nicht ein neuer Abfrageplan für die gesamte Prozedur erstellt werden soll, sondern nur für eine einzelne Anweisung innerhalb derselben, können sie außer bei INSERT auch direkt in einer einzelnen Abfrage auf der obersten Ebene angegeben werden. Dies gelingt über die Anweisung OPTION ( <query_hint> [ ,...n ] ), welche der gesamten Abfrage folgt und in diesem Fall das einfache Schlüsselwort RECOMPILE enthält.

Verschlüsselung

Der Quelltext der Prozedur kann normalerweise über die Prozedur sp_helptext abgerufen werden. Es besteht allerdings die Möglichkeit, diesen Quelltext zu verschlüsseln, was über die Klausel ENCRYPTION gelingt. Benutzer ohne Zugriff auf die Systemtabellen können dann den Prozedurquelltext nicht mehr abrufen.

CREATE PROCEDURE Production.usp_GetProductByNumber 
 (@vProductNumber nvarchar(25))
WITH ENCRYPTION
AS BEGIN ...
GO
EXEC sp_helptext 'Production.usp_GetProductByNumber'

Cursor-Rückgabe

Eine besonders schöne Technik neben der Rückgabe von relationalen Ergebnismengen ist die Rückgabe eines Cursors, der dann in der äußeren Anwendung (hauptsächlich T-SQL) verarbeitet werden kann. Dies soll am nachfolgenden Beispiel demonstriert werden. Zunächst muss ein Parameter wiederum mit dem Schlüsselwort OUTPUT versehen werden, weil es sich ja tatsächlich um einen Ausgabeparameter handelt. Als Datentyp verwendet man CURSOR und VARYING, wenn die Struktur Ergebnismenge wechselnd ist. In diesem Zusammenhang wird also die schon verwendete Prozedur usp_GetProduct noch einmal abgewandelt. Weder eine Ergebnismenge noch eine zusammenfassender Text wird ausgegeben, sondern stattdessen ein Cursor, der die Tabelle Product anbietet und sie vorab mit Hilfe der anderen Parameter für Listenpreis, Farbe und Kategorie gefiltert hat.

Innerhalb der Prozedur öffnet man den als Cursor schon bekannt gemachten Ausgabeparameter über die SET-Anweisung und fügt die CURSOR-Klausel sowie natürlich die ihn konstituierende Abfrage hinzu. Vielmehr ist nicht mehr zu tun als den Cursor schließlich als letzte Anweisung der Prozedur über OPEN zu öffnen. Dies sorgt dafür, dass man in der äußeren Anwendung Zugriff auf diese Daten erhält.

CREATE PROCEDURE Production.usp_GetProduct (
  @vListPrice money,
  @vColor     nvarchar(15),
  @vCategory  int = 1,
  @cProducts  CURSOR VARYING OUTPUT)
AS BEGIN
SET @cProducts = CURSOR FORWARD_ONLY STATIC FOR
SELECT Name, Color, ProductSubcategoryID
  FROM Production.Product
 WHERE Color = @vColor
   AND ListPrice > @vListPrice
   AND ProductSubcategoryID = @vCategory
OPEN @cProducts
END