Specificare i parametriSpecify Parameters

Se si specificano parametri di procedura, i programmi chiamanti sono in grado di passare i valori nel corpo della procedura.By specifying procedure parameters, calling programs are able to pass values into the body of the procedure. Tali valori possono essere utilizzati per diversi scopi durante l'esecuzione della procedura.Those values can be used for a variety of purposes during procedure execution. Inoltre, i parametri di procedura possono restituire valori al programma chiamante se il parametro è contrassegnato come parametro OUTPUT.Procedure parameters can also return values to the calling program if the parameter is marked as an OUTPUT parameter.

Una procedura può disporre al massimo di 2100 parametri, a ciascuno dei quali vengono assegnati un nome, un tipo di dati e una direzione.A procedure can have a maximum of 2100 parameters; each assigned a name, data type, and direction. Facoltativamente, ai parametri possono essere assegnati i valori predefiniti.Optionally, parameters can be assigned default values.

Nella sezione seguente vengono fornite informazioni sul passaggio dei valori nei parametri e sulla modalità di utilizzo di ognuno degli attributi di parametro durante una chiamata alla procedura.The following section provides information about passing values into parameters and about how each of the parameter attributes is used during a procedure call.

Passaggio dei valori nei parametriPassing Values into Parameters

I valori dei parametri forniti con una chiamata alla procedura devono essere costanti o una variabile. Non è possibile utilizzare un nome di funzione come valore di parametro.The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Le variabili possono essere definite dall'utente oppure di sistema, ad esempio @@spid.</span><span class="sxs-lookup">Variables can be user-defined or system variables such as @@spid.</span></span>

Negli esempi seguenti viene illustrato il passaggio dei valori dei parametri alla procedura uspGetWhereUsedProductID.The following examples demonstrate passing parameter values to the procedure uspGetWhereUsedProductID. Viene illustrato come passare i parametri come costanti e variabili e utilizzare una variabile per passare il valore di una funzione.They illustrate how to pass parameters as constants and variables and also how to use a variable to pass the value of a function.

USE AdventureWorks2012;  
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  
-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();  
GO  
-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO  

Specifica dei nomi di parametriSpecifying Parameter Names

Quando si crea una procedura e si dichiara un nome di parametro, tale nome deve iniziare con un singolo carattere @ e deve essere univoco nell'ambito della procedura.When creating a procedure and declaring a parameter name, the parameter name must begin with a single @ character and must be unique in the scope of the procedure.

La denominazione dei parametri e l'assegnazione dei valori appropriati in modo esplicito a ogni parametro in una chiamata alla procedura consentono ai parametri di essere forniti in qualsiasi ordine.Explicitly naming the parameters and assigning the appropriate values to each parameter in a procedure call allows the parameters to be supplied in any order. Se ad esempio per la procedura my_proc sono previsti tre parametri denominati @first, @seconde @third, i valori passati alla procedura possono essere assegnati ai nomi dei parametri, ad esempio: EXECUTE my_proc @second = 2, @first = 1, @third = 3;For example, if the procedure my_proc expects three parameters named @first, @second, and @third, the values passed to the procedure can be assigned to the parameter names, such as: EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Nota

Se un valore del parametro viene specificato nel formato @parameter =valore, tutti i parametri successivi devono essere specificati in questo modo.If one parameter value is supplied in the form @parameter =value, all subsequent parameters must be supplied in this manner. Se i valori dei parametri non vengono passati nel formato @parameter =valore, devono essere specificati nello stesso ordine, da sinistra a destra, dei parametri elencati nell'istruzione CREATE PROCEDURE.If the parameter values are not passed in the form @parameter =value, the values must be supplied in the identical order (left to right) as the parameters are listed in the CREATE PROCEDURE statement.

Avviso

Qualsiasi parametro passato nel formato @parameter =valore contenente un errore di ortografia causerà la generazione di un errore in SQL ServerSQL Server e impedirà l'esecuzione della procedura.Any parameter passed in the form @parameter =value with the parameter misspelled, will cause SQL ServerSQL Server to generate an error and prevent procedure execution.

Specifica dei tipi di dati per i parametriSpecifying Parameter Data Types

I parametri devono essere definiti con un tipo di dati quando vengono dichiarati in un'istruzione CREATE PROCEDURE.Parameters must be defined with a data type when they are declared in a CREATE PROCEDURE statement. Il tipo di dati di un parametro consente di determinare il tipo e l'intervallo di valori accettati per il parametro quando viene chiamata la procedura.The data type of a parameter determines the type and range of values that are accepted for the parameter when the procedure is called. Se ad esempio si definisce un parametro con un tipo di dati tinyint , verranno accettati solo i valori numerici nell'intervallo compreso tra 0 e 255 quando vengono passati in tale parametro.For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to 255 are accepted when passed into that parameter. Se una procedura viene eseguita con un valore incompatibile con il tipo di dati, verrà restituito un errore.An error is returned if a procedure is executed with a value incompatible with the data type.

Specifica dei valori predefiniti per i parametriSpecifying Parameter Default Values

Un parametro è considerato facoltativo se dispone di un valore predefinito specificato al momento della relativa dichiarazione.A parameter is considered optional if the parameter has a default value specified when it is declared. Non è necessario fornire un valore per un parametro facoltativo in una chiamata alla procedura.It is not necessary to provide a value for an optional parameter in a procedure call.

Il valore predefinito di un parametro viene utilizzato quando:The default value of a parameter is used when:

  • Non viene specificato alcun valore nella chiamata alla procedura.No value for the parameter is specified in the procedure call.

  • Viene specificata la parola chiave DEFAULT come valore nella chiamata alla procedura.The DEFAULT keyword is specified as the value in the procedure call.

Nota

Se il valore predefinito è una stringa di caratteri contenente spazi vuoti o punteggiatura o se inizia con un numero, ad esempio 6xxx, è necessario racchiuderlo tra virgolette singole.If the default value is a character string that contains embedded blanks or punctuation, or if it starts with a number (for example, 6xxx), it must be enclosed in single, straight quotation marks.

Se per il parametro non può essere specificato in modo appropriato alcun valore come predefinito, specificare come tale NULL.If no value can be specified appropriately as a default for the parameter, specify NULL as the default. È consigliabile la restituzione di un messaggio personalizzato da parte della procedura se quest'ultima viene eseguita senza un valore per il parametro.It is a good idea to have the procedure return a customized message if the procedure is executed without a value for the parameter.

Nell'esempio seguente viene creata la procedura usp_GetSalesYTD con un parametro di input, @SalesPerson.The following example creates the usp_GetSalesYTD procedure with one input parameter, @SalesPerson. NULL viene assegnato come valore predefinito per il parametro e utilizzato nelle istruzioni di gestione degli errori per restituire un messaggio di errore personalizzato nei casi in cui la procedura venga eseguita senza un valore per il parametro @SalesPerson .NULL is assigned as the default value for the parameter and is used in error handling statements to return a custom error message for cases when the procedure is executed without a value for the @SalesPerson parameter.

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

Di seguito è riportato un esempio di esecuzione della procedura.The following example executes the procedure. Tramite la prima istruzione la procedura viene eseguita senza specificare un valore di input.The first statement executes the procedure without specifying an input value. Tale operazione determina la restituzione del messaggio di errore personalizzato da parte delle istruzioni di gestione degli errori nella procedura.This causes the error handling statements in the procedure to return the custom error message. Tramite la seconda istruzione viene fornito un valore di input e viene restituito il set di risultati previsto.The second statement supplies an input value and returns the expected result set.

-- Run the procedure without specifying an input value.  
EXEC Sales.usp_GetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.usp_GetSalesYTD N'Blythe';  
GO  

Sebbene sia possibile omettere i parametri per cui sono stati forniti valori predefiniti, è possibile troncare soltanto l'elenco di parametri.Although parameters for which defaults have been supplied can be omitted, the list of parameters can only be truncated. Ad esempio, se una procedura dispone di cinque parametri, è possibile omettere sia il quarto sia il quinto parametro.For example, if a procedure has five parameters, both the fourth and the fifth parameters can be omitted. Tuttavia, non è possibile ignorare il quarto parametro finché è incluso il quinto, a meno che i parametri non vengano specificati nel formato @parameter =valore.However the fourth parameter cannot be skipped as long as the fifth parameter is included, unless the parameters are supplied in the form @parameter =value.

Specifica della direzione di un parametroSpecifying Parameter Direction

La direzione di un parametro può essere input, cioè un valore viene passato nel corpo della procedura, o output, vale a dire che tramite la procedura viene restituito un valore al programma chiamante.The direction of a parameter is either input, a value is passed into the body of the procedure, or output, the procedure returns a value to the calling program. Il parametro di input è l'impostazione predefinita.The default is an input parameter.

Per specificare un parametro di output, è necessario includere la parola chiave OUTPUT nella definizione del parametro nell'istruzione CREATE PROCEDURE.To specify an output parameter, the OUTPUT keyword must be specified in the definition of the parameter in the CREATE PROCEDURE statement. Tramite la procedura, al programma chiamante viene restituito il valore corrente del parametro di output quando la procedura è disponibile.The procedure returns the current value of the output parameter to the calling program when the procedure exits. Nel programma chiamante deve inoltre essere utilizzata la parola chiave OUTPUT quando si esegue la procedura per salvare il valore del parametro in una variabile utilizzabile nel programma chiamante.The calling program must also use the OUTPUT keyword when executing the procedure to save the parameter's value in a variable that can be used in the calling program.

Nell'esempio seguente viene creata la procedura Production.uspGetList , mediante la quale viene restituito un elenco di prodotti i cui prezzi non superano un determinato importo.The following example creates the Production.uspGetList procedure, which returns a list of products that have prices that do not exceed a specified amount. Nell'esempio viene illustrato l'utilizzo di più istruzioni SELECT e di più parametri OUTPUT.The example shows using multiple SELECT statements and multiple OUTPUT parameters. I parametri OUTPUT consentono a una procedura esterna, a un batch o a più istruzioni Transact-SQLTransact-SQL di accedere a un valore impostato durante l'esecuzione della procedura.OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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

Eseguire usp_GetList per restituire un elenco dei prodotti di Adventure WorksAdventure Works (biciclette) con un prezzo inferiore a 700 dollari.Execute usp_GetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. I parametri OUTPUT @cost e @compareprices vengono usati con elementi del linguaggio per il controllo di flusso per restituire un messaggio nella finestra Messaggi .The OUTPUT parameters @cost and @compareprices are used with control-of-flow language to return a message in the Messages window.

Nota

La variabile OUTPUT deve essere definita durante la creazione della procedura e durante l'utilizzo della variabile.The OUTPUT variable must be defined during the procedure creation and also during the use of the variable. Il nome di parametro e quello della variabile non devono corrispondere.The parameter name and variable name do not have to match. Il tipo di dati e la posizione del parametro devono tuttavia corrispondere, a meno che non si usi @listprice= variabile.However, the data type and parameter positioning must match (unless @listprice= variable is used).

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

Di seguito è riportato il set di risultati parziale:Here is the partial result set:

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.  

Vedere ancheSee Also

CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)