Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric
Aufrufende Programme sind in der Lage, durch die Angabe von Prozedurparametern Werte in den Textkörper der Prozedur zu übergeben. Diese Werte können für verschiedene Zwecke während der Prozedurausführung verwendet werden. Prozedurparameter können auch Werte an das aufrufende Programm zurückgeben, wenn der Parameter als OUTPUT Parameter markiert ist.
Eine Prozedur kann maximal 2.100 Parameter aufweisen; jedem zugewiesenen Namen, Datentyp und Richtung. Optional können Parametern Standardwerte zugewiesen werden.
Der folgende Abschnitt enthält Informationen zur Übergabe von Werten in Parameter und zur Verwendung der verschiedenen Parameterattribute in einem Prozeduraufruf.
Note
Weitere Informationen finden Sie in der AdventureWorks-Reihe von Beispieldatenbanken für die Übungen in diesem Artikel. Weitere Informationen finden Sie in den AdventureWorks-Beispieldatenbanken.
Übergeben von Werten an Parameter
Die mit einem Prozeduraufruf angegebenen Parameterwerte müssen Konstanten oder Variablen sein. Ein Funktionsname kann nicht als Parameterwert verwendet werden. Variablen können benutzerdefinierte Variablen oder Systemvariablen sein, wie z. B. @@spid.
Die folgenden Beispiele zeigen, wie Parameterwerte an die Prozedur uspGetWhereUsedProductIDübergeben werden. Sie zeigen, wie Parameter als Konstanten und Variablen übergeben werden, sowie die Verwendung einer Variablen, um den Wert einer Funktion zu übergeben.
USE AdventureWorks2022;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
Im folgenden Beispiel wird ein Fehler zurückgegeben, weil eine Funktion nicht als Parameterwert übergeben werden kann.
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
Verwenden Sie stattdessen eine Variable, um einen Funktionswert an den Parameter zu übergeben, wie im folgenden Beispiel gezeigt:
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
Angeben von Parameternamen
Wenn eine Prozedur erstellt und ein Parameternamen deklariert wird, muss ein Parametername gewählt werden, der mit einem einzelnen @-Zeichen beginnt und im Bereich der Prozedur eindeutig ist.
Durch das explizite Benennen der Parameter und Zuweisen der entsprechenden Werte zu jedem Parameter in einem Prozeduraufruf ist es möglich, dass die Parameter in beliebiger Reihenfolge angegeben werden. Wenn z. B. die Prozedur my_proc drei Parameter mit den Namen @first, @second und @third erwartet, können die Werte, die an die Prozedur übergeben werden, den Parameternamen zugewiesen werden, wie z. B.: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Note
Wenn ein Parameterwert im Format @parameter = value angegeben wird, müssen auch alle nachfolgenden Parameter auf diese Weise angegeben werden. Wenn die Parameterwerte nicht im Formular @parameter = valueübergeben werden, müssen die Werte in der identischen Reihenfolge (von links nach rechts) angegeben werden, da die Parameter in der CREATE PROCEDURE Anweisung aufgeführt werden. Es empfiehlt sich, Parameternamen anzugeben, sowohl zur besseren Lesbarkeit als auch zur Kompatibilität mit zukünftigen Versionen der gespeicherten Prozedur.
Warning
Jeder Parameter, der im Format @parameter = value übergeben und falsch geschrieben wird, bewirkt, dass SQL Server einen Fehler generiert und die Prozedurausführung beendet.
Angeben von Parameterdatentypen
Parameter müssen mit einem Datentyp definiert werden, wenn sie in einer CREATE PROCEDURE Anweisung deklariert werden. Durch den Datentyp eines Parameters werden der Typ und der Wertebereich festgelegt, die beim Aufruf der Prozedur für den Parameter akzeptiert werden. Wenn Sie z. B. einen Parameter mit dem tinyint -Datentyp definieren, werden nur numerische Werte im Bereich von 0 bis 255 als Werte für diesen Parameter akzeptiert. Wenn eine Prozedur mit einem Wert ausgeführt wird, der nicht mit dem Datentyp kompatibel ist, wird ein Fehler zurückgegeben.
Angeben von Parameterstandardwerten
Ein Parameter, für den in der Parameterdeklaration ein Standardwert angeben wird, wird als optional betrachtet. Es ist nicht notwendig, in einem Prozeduraufruf einen Wert für einen optionalen Parameter bereitzustellen.
Der Standardwert eines Parameters wird verwendet, wenn:
- Für den Parameter im Prozeduraufruf kein Wert angegeben wird.
- Das
DEFAULTSchlüsselwort wird als Wert im Prozeduraufruf angegeben.
Note
Wenn es sich bei dem Standardwert um eine Zeichenfolge handelt, die eingebettete Leerzeichen oder Satzzeichen enthält oder mit einer Zahl beginnt (z. B. 6abc), muss der Wert in einfache, gerade Anführungszeichen eingeschlossen werden.
Note
Standardparameter werden in Azure Synapse Analytics oder Analytics Platform System (PDW) nicht unterstützt.
Wenn kein entsprechender Wert als Standardwert für den Parameter angegeben werden kann, geben Sie NULL als Standardwert an. Es empfiehlt sich, die Prozedur eine benutzerdefinierte Meldung zurückgeben zu lassen, wenn sie ohne Wertangabe für den Parameter ausgeführt wird.
Im folgenden Beispiel wird die gespeicherte Prozedur uspGetSalesYTD mit einem Eingabeparameter ( @SalesPerson) ausgeführt.
NULL wird als Standardwert für den Parameter zugewiesen und in Fehlerbehandlungsanweisungen zum Zurückgeben einer benutzerdefinierten Fehlermeldung verwendet, wenn die Prozedur ohne einen Wert für den Parameter @SalesPerson ausgeführt wird.
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL -- NULL default value
AS
SET NOCOUNT ON;
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN
END
-- Get the sales for the specified sales person and
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
Im folgenden Beispiel wird die Prozedur ausgeführt. Die erste Anweisung führt die Prozedur ohne Angabe eines Eingabewerts aus. Dies bewirkt, dass die Fehlerbehandlungsanweisungen in der Prozedur die benutzerdefinierte Fehlermeldung zurückgeben. Die zweite Anweisung stellt einen Eingabewert zur Verfügung und gibt das erwartete Resultset zurück.
-- Run the procedure without specifying an input value.
EXEC Sales.uspGetSalesYTD;
GO
-- Run the procedure with an input value.
EXEC Sales.uspGetSalesYTD N'Blythe';
GO
Sie können Parameter auslassen, für die Standardwerte angegeben wurden, die Liste der Non-Nullable-Parameter kann aber nur abgeschnitten werden. Wenn eine Prozedur z. B. über fünf Parameter ohne Angabe der Parameternamen mit @parameter = value verfügt, können sowohl der vierte als auch der fünfte Parameter weggelassen werden. Der vierte Parameter kann jedoch nicht übersprungen werden, solange der fünfte Parameter eingeschlossen ist, außer wenn die Parameter im Format @parameter = value angegeben werden.
Angeben mehrerer Parameter mit Standardwerten
Sie können Parameter weglassen, wenn Sie die Parameternamen angeben. Beachten Sie die folgende gespeicherte Prozedur mit mehreren optionalen Parametern mit NULL-Standardwerten.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspSearchList;
GO
CREATE PROCEDURE Production.uspSearchList
@ListPrice money
, @ProductCategoryID int = NULL -- NULL default value
, @ProductSubcategoryID int = NULL -- NULL default value
, @ProductBusinessEntityID int = NULL -- NULL default value
AS
SET NOCOUNT ON;
SELECT
p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
FROM
Production.Product AS p
INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
WHERE (p.ListPrice < @ListPrice)
AND (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
AND (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
AND (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO
Sie können Parameter mit Standardwerten angeben oder weglassen, wie in den folgenden Reihe von Beispielen veranschaulicht, solange jeder mit seinem Parameternamen im Format @parameter = value übergeben wird:
--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;
Das folgende Beispiel ist keine gültige T-SQL-Syntax, weil alle nachfolgenden Parameter auf die gleiche Weise übergeben werden müssen, sobald ein Parametername angegeben wird. Das Angeben von Parameternamen für alle Werte ist immer zu empfehlen und verhindert Fehler und Verwirrung.
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
Parameterrichtung angeben
Die Parameterrichtung ist entweder Eingabe, d. h. ein Wert wird in den Textkörper der Prozedur übergeben, oder Ausgabe, d. h. die Prozedur gibt einen Wert an das aufrufende Programm zurück. Standardmäßig wird ein Eingabeparameter verwendet.
Um einen Ausgabeparameter anzugeben, muss das OUTPUT Schlüsselwort in der Definition des Parameters in der CREATE PROCEDURE Anweisung angegeben werden. Die Prozedur gibt den aktuellen Wert des Ausgabeparameters an das aufrufende Programm zurück, wenn die Prozedur beendet wird. Das aufrufende Programm muss auch das OUTPUT Schlüsselwort verwenden, wenn die Prozedur ausgeführt wird, um den Wert des Parameters in einer Variablen zu speichern, die im aufrufenden Programm verwendet werden kann.
Im nachfolgenden Beispiel wird die Production.usp_GetList-Prozedur erstellt, von der eine Liste der Produkte zurückgegeben wird, deren Preis einen angegebenen Betrag nicht übersteigt. Das Beispiel zeigt die Verwendung mehrerer SELECT Anweisungen und mehrerer OUTPUT Parameter.
OUTPUT Parameter ermöglichen es einer externen Prozedur, einem Batch oder mehreren Transact-SQL-Anweisung, während der Prozedurausführung auf einen Wert zuzugreifen, der festgelegt wurde.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList
@Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
Führen Sie usp_GetList aus, um eine Liste der Adventure Works-Produkte (Bikes) zurückzugeben, die weniger als 700 USD kosten. Die OUTPUT Parameter @cost und @compareprices werden mit der Ablaufsteuerungssprache verwendet, um eine Nachricht im Fenster "Nachrichten " zurückzugeben.
Note
Die OUTPUT Variable muss während der Prozedurerstellung und auch während der Verwendung der Variablen definiert werden. Parametername und Variablenname brauchen nicht übereinzustimmen. Jedoch müssen der Datentyp und die Position des Parameters übereinstimmen (es sei denn, es wird @listprice = variable verwendet).
DECLARE @ComparePrice money, @Cost money ;
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';
Im Folgenden wird ein Teil des Resultsets aufgeführt:
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.