Angeben von Parametern in einer gespeicherten Prozedur
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Aufrufende Programme sind in der Lage, durch die Angabe von Prozedurparametern Werte in den Textkörper der Prozedur zu übergeben. Jene Werte können während der Prozedurausführung zu einer Vielzahl von Zwecken verwendet werden. Prozedurparameter können auch Werte an das aufrufende Programm zurückgeben, wenn der Parameter als OUTPUT-Parameter markiert wird.
Eine Prozedur kann über maximal 2100 Parameter verfügen, denen jeweils ein Name, eine Datentyp und eine Richtung zugewiesen wird. 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.
Hinweis
Weitere Informationen zu den AdventureWorks Übungen in diesem Artikel finden Sie in der Reihe von Beispieldatenbanken. 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 Oder Systemvariablen wie @@spidsein.
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, da 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 die Prozedur my_proc beispielsweise drei Parameter mit dem Namen @first, @secondund @thirderwartet, können die an die Prozedur übergebenen Werte den Parameternamen zugewiesen werden, z. B.: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Hinweis
Wenn ein Parameterwert im Format @parameter = valueangegeben wird, müssen alle nachfolgenden Parameter auf diese Weise angegeben werden. Wenn die Parameterwerte nicht im Format @parameter = valueübergeben werden, müssen die Werte in der gleichen Reihenfolge (von links nach rechts) angegeben werden, da die Parameter in der CREATE PROCEDURE-Anweisung aufgeführt sind. Es empfiehlt sich, Parameternamen anzugeben, um eine bessere Lesbarkeit und Kompatibilität mit zukünftigen Versionen der gespeicherten Prozedur zu gewährleisten.
Warnung
Jeder Parameter, der im Formular @parameter = value mit falsch geschriebenem Parameter übergeben wird, führt dazu, dass SQL Server einen Fehler generiert und die Ausführung von Prozeduren verhindert.
Angeben von Parameterdatentypen
Parameter müssen mit einem Datentyp definiert werden, wann 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 Standardwerten für Parameter
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 DEFAULT-Schlüsselwort im Prozeduraufruf als Wert für den Parameter angegeben wird.
Hinweis
Wenn der Standardwert eine Zeichenfolge ist, die eingebettete Leerzeichen oder Interpunktionszeichen enthält, oder wenn er mit einer Zahl beginnt (z. B 6abc. ), muss er in einfache, gerade Anführungszeichen eingeschlossen werden.
Hinweis
Standardparameter werden in Azure Synapse Analytics oder Analytics Platform System (PDW) nicht unterstützt.
Wenn kein Wert entsprechend als Standard für den Parameter angegeben werden kann, geben Sie NULL als Standard 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 verwendet, um eine benutzerdefinierte Fehlermeldung für Fälle zurückzugeben, in denen die Prozedur ohne einen Wert für den @SalesPerson Parameter 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
Obwohl Parameter, für die Standardwerte angegeben wurden, weggelassen werden können, kann die Liste der Parameter, die keine Nullwerte zulassen, nur abgeschnitten werden. Wenn z. B. eine Prozedur über fünf Parameter verfügt, ohne die Parameternamen mit anzugeben @parameter = value, können der vierte und der fünfte Parameter weggelassen werden. Der vierte Parameter kann jedoch nicht übersprungen werden, solange der fünfte Parameter enthalten ist, es sei denn, die Parameter werden im Format @parameter = valueangegeben.
Angeben mehrerer Parameter mit Standardwerten
Sie können Parameter weglassen, wenn Sie die Parameternamen angeben. Betrachten 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 die Reihe der folgenden Beispiele veranschaulicht, solange jeder mit seinem Parameternamen im Format @parameter = valueangegeben 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 eine ungültige T-SQL-Syntax, da alle nachfolgenden Parameter auf die gleiche Weise bereitgestellt werden müssen, sobald ein Parametername angegeben wurde. Die Angabe von Parameternamen für alle Werte wird immer empfohlen und verhindert Fehler und Verwechslungen.
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
Angeben der Parameterrichtung
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 müssen Sie das OUTPUT-Schlüsselwort in der Definition des Parameters in der CREATE PROCEDURE-Anweisung angeben. Die Prozedur gibt den aktuellen Wert des Ausgabeparameters an das aufrufende Programm zurück, wenn die Prozedur beendet wird. Um den Wert des Parameters in einer Variablen zu speichern, die in dem aufrufenden Programm verwendet werden kann, muss das aufrufende Programm beim Ausführen der Prozedur ebenfalls das Schlüsselwort OUTPUT verwenden.
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. In dem Beispiel wird die Verwendung mehrerer SELECT-Anweisungen und mehrerer OUTPUT-Parameter dargestellt. OUTPUT-Parameter ermöglichen einer externen Prozedur, einem Batch oder mehreren Transact-SQL-Anweisungen während der Ausführung der Prozedur den Zugriff auf einen Satz von Werten.
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 aus usp_GetList , um eine Liste von Adventure Works-Produkten (Bikes) zurückzugeben, die weniger als 700 USD kosten. Die OUTPUT-Parameter @cost und @compareprices werden mit einer Ablaufsteuerungssprache verwendet, um eine Meldung an das Fenster Meldungen zurückzugeben.
Hinweis
Die OUTPUT-Variable muss sowohl beim Erstellen der Prozedur als auch beim Verwenden der Variable definiert werden. Parametername und Variablenname brauchen nicht übereinzustimmen. Der Datentyp und die Parameterpositionierung müssen jedoch übereinstimmen (es sei denn @listprice = variable , es wird 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.
Nächste Schritte
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für