Parameters opgeven in een opgeslagen procedure

Artikel
03/05/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL-database in Microsoft Fabric

Door procedureparameters op te geven, kunnen aanroepende programma's waarden doorgeven in de hoofdtekst van de procedure. Deze waarden kunnen voor verschillende doeleinden worden gebruikt tijdens het uitvoeren van de procedure. Procedureparameters kunnen ook waarden retourneren aan het aanroepende programma als de parameter is gemarkeerd als een OUTPUT-parameter.

Een procedure kan maximaal 2100 parameters bevatten; elke toegewezen naam, gegevenstype en richting. Optioneel kunnen parameters worden toegewezen aan standaardwaarden.

De volgende sectie bevat informatie over het doorgeven van waarden aan parameters en over hoe elk van de parameterkenmerken wordt gebruikt tijdens een procedure-aanroep.

Notitie

Raadpleeg de AdventureWorks reeks voorbeelddatabases voor de oefeningen van dit artikel. Zie AdventureWorks-voorbeelddatabasesvoor meer informatie.

waarden doorgeven aan parameters

De parameterwaarden die worden geleverd met een procedure-aanroep, moeten constanten of een variabele zijn; een functienaam kan niet worden gebruikt als parameterwaarde. Variabelen kunnen door de gebruiker gedefinieerde of systeemvariabelen zijn, zoals @@spid.

In de volgende voorbeelden ziet u hoe parameterwaarden worden doorgegeven aan de procedure uspGetWhereUsedProductID. Ze laten zien hoe u parameters doorgeeft als constanten en variabelen en hoe u ook een variabele gebruikt om de waarde van een functie door te geven.

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

In het volgende voorbeeld wordt een fout geretourneerd omdat een functie niet kan worden doorgegeven als parameterwaarde.

-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();

Gebruik in plaats daarvan een variabele om een functiewaarde door te geven aan de parameter, zoals in het volgende voorbeeld:

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO

parameternamen opgeven

Wanneer u een procedure maakt en een parameternaam declareren, moet de parameternaam beginnen met één @ teken en moet deze uniek zijn binnen het bereik van de procedure.

Door de parameters expliciet een naam te geven en de juiste waarden toe te wijzen aan elke parameter in een procedureoproep, kunnen de parameters in elke volgorde worden opgegeven. Als de procedure bijvoorbeeld my_proc drie parameters verwacht met de naam @first, @seconden @third, kunnen de waarden die aan de procedure worden doorgegeven, worden toegewezen aan de parameternamen, zoals: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Notitie

Als één parameterwaarde wordt opgegeven in het formulier @parameter = value, moeten alle volgende parameters op deze manier worden opgegeven. Als de parameterwaarden niet worden doorgegeven in het formulier @parameter = value, moeten de waarden worden opgegeven in dezelfde volgorde (van links naar rechts) omdat de parameters worden vermeld in de instructie CREATE PROCEDURE. Het is een goede gewoonte om parameternamen op te geven, zowel voor superieure leesbaarheid als compatibiliteit met toekomstige versies van de opgeslagen procedure.

Waarschuwing

Elke parameter die is doorgegeven in het formulier @parameter = value met de parameter onjuist gespeld, zorgt ervoor dat SQL Server een fout genereert en de uitvoering van de procedure verhindert.

parametergegevenstypen opgeven

Parameters moeten worden gedefinieerd met een gegevenstype wanneer ze worden gedeclareerd in een CREATE PROCEDURE-instructie. Het gegevenstype van een parameter bepaalt het type en het bereik van waarden die worden geaccepteerd voor de parameter wanneer de procedure wordt aangeroepen. Als u bijvoorbeeld een parameter met een kleineint gegevenstype definieert, worden alleen numerieke waarden tussen 0 en 255 geaccepteerd wanneer deze in die parameter wordt doorgegeven. Er wordt een fout geretourneerd als een procedure wordt uitgevoerd met een waarde die niet compatibel is met het gegevenstype.

standaardwaarden voor parameters opgeven

Een parameter wordt als optioneel beschouwd als de parameter een standaardwaarde heeft die is opgegeven wanneer deze wordt gedeclareerd. Het is niet nodig om een waarde op te geven voor een optionele parameter in een procedureaanroep.

De standaardwaarde van een parameter wordt gebruikt wanneer:

Er wordt geen waarde voor de parameter opgegeven in de procedure-aanroep.
Het trefwoord DEFAULT wordt opgegeven als de waarde in de procedure-aanroep.

Notitie

Als de standaardwaarde een tekenreeks is die ingesloten lege waarden of interpunctie bevat, of als deze begint met een getal (bijvoorbeeld 6abc), moet deze tussen enkele rechte aanhalingstekens worden geplaatst.

Notitie

Standaardparameters worden niet ondersteund in Azure Synapse Analytics of Analytics Platform System (PDW).

Als er geen waarde kan worden opgegeven als een standaardwaarde voor de parameter, geeft u NULL op als de standaardwaarde. Het is een goed idee om de procedure een aangepast bericht te laten retourneren als de procedure wordt uitgevoerd zonder een waarde voor de parameter.

In het volgende voorbeeld wordt de uspGetSalesYTD procedure gemaakt met één invoerparameter, @SalesPerson. NULL wordt toegewezen als de standaardwaarde voor de parameter en wordt gebruikt in instructies voor foutafhandeling om een aangepast foutbericht te retourneren voor gevallen waarin de procedure wordt uitgevoerd zonder een waarde voor de parameter @SalesPerson.

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

In het volgende voorbeeld wordt de procedure uitgevoerd. Met de eerste instructie wordt de procedure uitgevoerd zonder een invoerwaarde op te geven. Hierdoor zorgen de instructies voor foutafhandeling in de procedure ervoor dat het aangepaste foutbericht wordt geretourneerd. De tweede instructie levert een invoerwaarde op en retourneert de verwachte resultatenset.

-- 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

Hoewel parameters waarvoor standaardwaarden zijn opgegeven, kunnen worden weggelaten, kan de lijst met niet-null-parameters alleen worden afgekapt. Als een procedure bijvoorbeeld vijf parameters heeft, zonder de parameternamen met een @parameter = valueop te geven, kunnen de vierde en de vijfde parameters worden weggelaten. De vierde parameter kan echter niet worden overgeslagen zolang de vijfde parameter is opgenomen, tenzij de parameters worden opgegeven in het formulier @parameter = value.

Meerdere parameters opgeven met standaardwaarden

U kunt parameters weglaten als u de parameternamen opgeeft. Houd rekening met de volgende opgeslagen procedure met meerdere optionele parameters met NULL standaardwaarden.

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

U kunt parameters opgeven of weglaten met standaardwaarden, zoals de reeks van de volgende voorbeelden laat zien, zolang elke parameters wordt opgegeven met de parameternaam in het formulier @parameter = value:

--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;

Het volgende voorbeeld is geen geldige T-SQL-syntaxis, omdat alle volgende parameters op dezelfde manier moeten worden opgegeven, zodra een parameternaam is opgegeven. Het opgeven van parameternamen voor alle waarden wordt altijd aanbevolen en voorkomt fouten en verwarring.

EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;

parameterrichting opgeven

De richting van een parameter is ofwel invoer, waarbij een waarde wordt doorgegeven aan het proces, of uitvoer, waarbij de procedure een waarde retourneert aan het aanroepende programma. De standaardwaarde is een invoerparameter.

Als u een uitvoerparameter wilt opgeven, moet het sleutelwoord OUTPUT worden opgegeven in de definitie van de parameter in de instructie CREATE PROCEDURE. De procedure retourneert de huidige waarde van de uitvoerparameter naar het aanroepende programma wanneer de procedure wordt afgesloten. Het aanroepende programma moet ook het trefwoord UITVOER gebruiken bij het uitvoeren van de procedure om de waarde van de parameter op te slaan in een variabele die kan worden gebruikt in het aanroepende programma.

In het volgende voorbeeld wordt de Production.usp_GetList procedure gemaakt, waarmee een lijst wordt geretourneerd met producten met prijzen die niet hoger zijn dan een opgegeven bedrag. In het voorbeeld ziet u hoe u meerdere SELECT-instructies en meerdere OUTPUT-parameters gebruikt. MET UITVOERparameters kunnen een externe procedure, een batch of meer dan één Transact-SQL instructie toegang krijgen tot een waardeset tijdens de uitvoering van de procedure.

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

Voer usp_GetList uit om een lijst met Adventure Works-producten (Bikes) te retourneren die minder dan $ 700 kosten. De UITVOERparameters @cost en @compareprices worden gebruikt met de besturingstaal voor stroomregeling om een bericht weer te geven in het venster Berichten.

Notitie

De UITVOERvariabele moet worden gedefinieerd tijdens het maken van de procedure en ook tijdens het gebruik van de variabele. De parameternaam en de naam van de variabele hoeven niet overeen te komen. Het gegevenstype en de parameterpositie moeten echter overeenkomen (tenzij @listprice = variable wordt gebruikt).

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)))+'.';

Dit is de gedeeltelijke resultatenset:

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.

PROCEDURE maken (Transact-SQL)
parameters
EXECUTE (Transact-SQL)

Delen via

Parameters opgeven in een opgeslagen procedure

waarden doorgeven aan parameters

parameternamen opgeven

parametergegevenstypen opgeven

standaardwaarden voor parameters opgeven

Meerdere parameters opgeven met standaardwaarden

parameterrichting opgeven

Feedback

Aanvullende resources

Delen via

Parameters opgeven in een opgeslagen procedure

waarden doorgeven aan parameters

parameternamen opgeven

parametergegevenstypen opgeven

standaardwaarden voor parameters opgeven

Meerdere parameters opgeven met standaardwaarden

parameterrichting opgeven

Verwante inhoud

Feedback

Aanvullende resources