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

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Crea un Transact-SQLTransact-SQL o common language runtime (CLR) stored procedure SQL ServerSQL Server, Database SQL di AzureAzure SQL Database, Azure SQL Data Warehouse e Parallel Data Warehouse.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Database SQL di AzureAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Le stored procedure sono simili alle procedure di altri linguaggi di programmazione in quanto sono in grado di:Stored procedures are similar to procedures in other programming languages in that they can:

  • Accettare parametri di input e restituire più valori sotto forma di parametri di output alla procedura o al batch che esegue la chiamata.Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • Includere istruzioni di programmazione che eseguono le operazioni nel database, tra cui la chiamata di altre procedure.Contain programming statements that perform operations in the database, including calling other procedures.

  • Restituire un valore di stato a una procedura o a un batch che esegue la chiamata per indicare l'esito positivo o negativo (e il motivo dell'esito negativo).Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

    Utilizzare questa istruzione per creare una stored procedure permanenti nel database corrente o una stored procedure temporanea nel tempdb database.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

Nota

Integrazione di CLR di .NET Framework in SQL Server viene illustrata in questo argomento.The integration of .NET Framework CLR into SQL Server is discussed in this topic. Integrazione con CLR non si applica ad Azure Database SQLSQL Database.CLR integration does not apply to Azure Database SQLSQL Database.

Passare a semplici esempi per ignorare i dettagli della sintassi e ottenere un esempio semplice di un base stored procedure.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintassiSyntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
[ WITH <procedure_option> [ ,...n ] ]  
[ FOR REPLICATION ]   
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]  

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
-- Transact-SQL Syntax for CLR Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }   
        [ = default ] [ OUT | OUTPUT ] [READONLY]  
    ] [ ,...n ]   
[ WITH EXECUTE AS Clause ]  
AS { EXTERNAL NAME assembly_name.class_name.method_name }  
[;]  
-- Transact-SQL Syntax for Natively Compiled Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] 
        [ OUT | OUTPUT ] [READONLY] 
    ] [ ,... n ]  
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]  
AS  
{  
  BEGIN ATOMIC WITH (set_option [ ,... n ] )  
sql_statement [;] [ ... n ]  
 [ END ]  
}  
 [;]  

<set_option> ::=  
    LANGUAGE =  [ N ] 'language'  
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }  
  | [ DATEFIRST = number ]  
  | [ DATEFORMAT = format ]  
  | [ DELAYED_DURABILITY = { OFF | ON } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  

-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

ArgomentiArguments

O ALTEROR ALTER
Si applica a: Azure Database SQLSQL Database, SQL ServerSQL Server (a partire da SQL Server 2016SQL Server 2016 SP1).Applies to: Azure Database SQLSQL Database, SQL ServerSQL Server (starting with SQL Server 2016SQL Server 2016 SP1).

Modifica la procedura, se esiste già.Alters the procedure if it already exists.

schema_nameschema_name
Nome dello schema a cui appartiene la procedura.The name of the schema to which the procedure belongs. Le procedure sono associate a schema.Procedures are schema-bound. Se durante la creazione della procedura non viene specificato un nome dello schema, viene assegnato automaticamente lo schema predefinito dell'utente che sta creando la procedura.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
Nome della procedura.The name of the procedure. I nomi delle procedure devono essere conformi alle regole per identificatori e deve essere univoco all'interno dello schema.Procedure names must comply with the rules for identifiers and must be unique within the schema.

Evitare di utilizzare il sp _ prefisso per la denominazione delle procedure.Avoid the use of the sp_ prefix when naming procedures. Questo prefisso viene usato da SQL ServerSQL Server per definire le procedure di sistema.This prefix is used by SQL ServerSQL Server to designate system procedures. L'utilizzo del prefisso può comportare l'interruzione del codice dell'applicazione, se è presente una procedura di sistema con lo stesso nome.Using the prefix can cause application code to break if there is a system procedure with the same name.

Stored procedure temporanee locali o globali possono essere create utilizzando un simbolo di cancelletto (#) prima di procedure_name (#procedure_name) per stored procedure temporanee locali e due simboli di cancelletto per temporanea globale procedure (# # procedure_name).Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Una stored procedure temporanea locale è visibile solo alla connessione da cui è stata creata e, alla chiusura di quest'ultima, viene eliminata.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. Una stored procedure temporanea globale è disponibile per tutte le connessioni e viene eliminata al termine dell'ultima sessione che la usano.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Non è possibile specificare nomi temporanei per le procedure CLR.Temporary names cannot be specified for CLR procedures.

Il nome completo di una procedura o di una stored procedure temporanea globale, inclusi i simboli ##, non deve superare i 128 caratteri.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. Il nome completo di una stored procedure temporanea locale, incluso il simbolo #, non deve superare i 116 caratteri.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; numero; number
Si applica a: SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Integer facoltativo usato per raggruppare le procedure con lo stesso nome.An optional integer that is used to group procedures of the same name. Tali procedure possono essere eliminate contemporaneamente tramite un'istruzione DROP PROCEDURE.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Nota

Questa funzionalità verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server.This feature will be removed in a future version of Microsoft SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Procedure numerate non è possibile utilizzare il xml o CLR definito dall'utente, tipi e non può essere utilizzato in una Guida di piano.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@parametro@ parameter
Parametro dichiarato nella procedura.A parameter declared in the procedure. Specificare un nome di parametro utilizzando il simbolo di chiocciola (@) come primo carattere.Specify a parameter name by using the at sign (@) as the first character. Il nome del parametro deve essere conforme alle regole per identificatori.The parameter name must comply with the rules for identifiers. Poiché i parametri sono locali rispetto alla procedura, è possibile usare gli stessi nomi di parametro in altre procedure.Parameters are local to the procedure; the same parameter names can be used in other procedures.

È possibile dichiarare uno o più parametri con un limite massimo di 2.100.One or more parameters can be declared; the maximum is 2,100. Il valore di ogni parametro dichiarato deve essere specificato dall'utente quando viene chiamata la procedura, a meno che non venga indicato un valore predefinito per il parametro oppure il valore venga impostato in modo da corrispondere a quello di un altro parametro.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. Se una stored procedure contiene parametri con valori di tabellae il parametro è presente nella chiamata, viene passata una tabella vuota.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. I parametri possono rappresentare solo espressioni costanti, non nomi di tabella, nomi di colonna o nomi di altri oggetti di database.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. Per altre informazioni, vedere EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

Se viene specificata l'opzione FOR REPLICATION, non è possibile dichiarare alcun parametro.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name.[ type_schema_name. ] data_type] data_type
Tipo di dati del parametro e schema a cui appartiene il tipo di dati.The data type of the parameter and the schema to which the data type belongs.

Linee guida per Transact-SQLTransact-SQL procedure:Guidelines for Transact-SQLTransact-SQL procedures:

  • Tutti Transact-SQLTransact-SQL tipi di dati possono essere usati come parametri.All Transact-SQLTransact-SQL data types can be used as parameters.

  • Per creare parametri con valori di tabella è possibile usare il tipo di tabella definito dall'utente.You can use the user-defined table type to create table-valued parameters. I parametri con valori di tabella possono essere solo parametri di input e devono essere associati alla parola chiave READONLY.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. Per ulteriori informazioni, vedere utilizzare parametri ( motore di Database )For more information, see Use Table-Valued Parameters (Database Engine)

  • cursore tipi di dati possono essere solo parametri OUTPUT e devono essere associati alla parola chiave VARYING.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

Linee guida per le procedure CLR:Guidelines for CLR procedures:

  • Tutti i tipi di dati nativi di SQL ServerSQL Server per cui è presente un equivalente nel codice gestito possono essere usati come parametri.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. Per ulteriori informazioni sulla corrispondenza tra tipi CLR e SQL ServerSQL Server tipi di dati di sistema, vedere Mapping dei dati di parametro CLR.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. Per ulteriori informazioni su SQL ServerSQL Server tipi di dati di sistema e la relativa sintassi, vedere tipi di dati ( Transact-SQL ) .For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • Con valori di tabella o cursore tipi di dati non possono essere utilizzati come parametri.Table-valued or cursor data types cannot be used as parameters.

  • Se al parametro è stato assegnato un tipo di dati CLR definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per il tipo.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
Specifica il set di risultati supportato come parametro di output.Specifies the result set supported as an output parameter. Questo parametro viene creato in modo dinamico dalla procedura e il relativo contenuto può variare.This parameter is dynamically constructed by the procedure and its contents may vary. Si applica solo a cursore parametri.Applies only to cursor parameters. Questa opzione non è valida per le procedure CLR.This option is not valid for CLR procedures.

impostazione predefinitadefault
Valore predefinito per un parametro.A default value for a parameter. Se un valore predefinito è definito per un parametro, è possibile eseguire la procedura senza specificare un valore per tale parametro.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. Il valore predefinito deve essere una costante oppure NULL.The default value must be a constant or it can be NULL. Il formato del valore della costante può essere un carattere jolly; in questo modo sarà possibile usare la parola chiave LIKE quando si passa il parametro nella procedura.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

I valori predefiniti vengono registrati nella sys colonna solo per le procedure CLR.Default values are recorded in the sys.parameters.default column only for CLR procedures. Tale colonna è NULL per Transact-SQLTransact-SQL parametri di routine.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
Indica che si tratta di un parametro di output.Indicates that the parameter is an output parameter. Utilizzare i parametri di output per restituire valori al chiamante della procedura.Use OUTPUT parameters to return values to the caller of the procedure. testo, ntext, e immagine parametri non possono essere utilizzati come parametri di OUTPUT, a meno che la procedura è una stored procedure CLR.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. Un parametro di output può essere un segnaposto del cursore, a meno che non si tratti di una procedura CLR.An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. Un tipo di dati con valori di tabella non può essere specificato come parametro di output di una procedura.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
Indica che il parametro non può essere aggiornato o modificato all'interno del corpo della procedura.Indicates that the parameter cannot be updated or modified within the body of the procedure. Se si tratta di un tipo di parametro con valori di tabella, è necessario specificare la parola chiave READONLY.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
Indica che il Motore di databaseDatabase Engine non memorizzati nella cache un piano di query per questa procedura, imponendo che devono essere elaborate ogni volta che viene eseguita.Indicates that the Motore di databaseDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. Per ulteriori informazioni sui motivi della ricompilazione forzata, vedere ricompilare una Stored Procedure.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. Questa opzione non può essere usata per procedure CLR o se si specifica FOR REPLICATION.This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

Per indicare il Motore di databaseDatabase Engine di ignorare i piani di query per le singole query all'interno di una stored procedure, utilizzare l'hint per la query RECOMPILE nella definizione della query.To instruct the Motore di databaseDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. Per altre informazioni, vedere Hint per la query (Transact-SQL).For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
Si applica a: SQL Server ( SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.

Indica che SQL ServerSQL Server converte il testo originale dell'istruzione CREATE PROCEDURE in un formato offuscato.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo in SQL ServerSQL Server.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. Il testo offuscato non può essere recuperato da utenti che non hanno accesso a file di database o tabelle di sistema.Users who have no access to system tables or database files cannot retrieve the obfuscated text. Tuttavia, il testo è disponibile per gli utenti con privilegi di accesso a tabelle di sistema tramite il porta DAC o accesso diretto a file di database.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. Inoltre, agli utenti che possono collegare un debugger al processo del server è consentito recuperare la procedura decrittografata dalla memoria in fase di esecuzione.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Per ulteriori informazioni sull'accesso ai metadati di sistema, vedere Metadata Visibility Configuration.For more information about accessing system metadata, see Metadata Visibility Configuration.

Questa opzione non è valida per le procedure CLR.This option is not valid for CLR procedures.

Le procedure create con questa opzione non possono essere pubblicate durante la replica di SQL ServerSQL Server.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS clausolaEXECUTE AS clause
Specifica il contesto di sicurezza in cui deve essere eseguita la procedura.Specifies the security context under which to execute the procedure.

Stored procedure compilate in modo nativo, a partire SQL Server 2016SQL Server 2016 e Database SQL di AzureAzure SQL Database, non sono previste limitazioni in EXECUTE AS clausola.For natively compiled stored procedures, starting SQL Server 2016SQL Server 2016 and in Database SQL di AzureAzure SQL Database, there are no limitations on the EXECUTE AS clause. In SQL Server 2014SQL Server 2014 il SELF, OWNER e 'user_name' clausole sono supportate con le stored procedure compilate in modo nativo.In SQL Server 2014SQL Server 2014 the SELF, OWNER, and ‘user_name’ clauses are supported with natively compiled stored procedures.

Per altre informazioni, vedere Clausola EXECUTE AS (Transact-SQL).For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
Si applica a: SQL Server ( SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Database SQL di AzureAzure SQL Database.

Specifica che la procedura viene creata per la replica.Specifies that the procedure is created for replication. Di conseguenza, non può essere eseguita nel Sottoscrittore.Consequently, it cannot be executed on the Subscriber. Una procedura creata con l'opzione FOR REPLICATION viene usata come filtro di procedura ed eseguita solo durante la replica.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Se viene specificata l'opzione FOR REPLICATION, non è possibile dichiarare alcun parametro.Parameters cannot be declared if FOR REPLICATION is specified. Inoltre, l'opzione FOR REPLICATION non può essere specificata per procedure CLR.FOR REPLICATION cannot be specified for CLR procedures. L'opzione RECOMPILE viene ignorata per le procedure create con l'opzione FOR REPLICATION.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

Oggetto FOR REPLICATION procedure è un tipo di oggetto RF in Sys. Objects e Procedures.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{[BEGIN] sql_statement [;] [ ... n ] [Fine]}{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Una o più istruzioni Transact-SQLTransact-SQL che includono il corpo della procedura.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. Per racchiudere le istruzioni è possibile usare le parole chiave facoltative BEGIN ed END.You can use the optional BEGIN and END keywords to enclose the statements. Per informazioni, vedere le sezioni Procedure consigliate, Osservazioni generali e Limitazioni e restrizioni riportate di seguito.For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

NOME esterno nome_assembly. class_name. nome_metodoEXTERNAL NAME assembly_name.class_name.method_name
Si applica a: SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017, Database SQLSQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Database SQLSQL Database.

Specifica il metodo di un assembly .NET Framework.NET Framework affinché una procedura CLR vi faccia riferimento.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. CLASS_NAME deve essere un valore valido SQL ServerSQL Server identificatore e deve esistere come classe nell'assembly.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. Se la classe dispone di un nome completo dello spazio dei nomi che utilizza un punto (.) per separare le varie parti dello spazio dei nomi, il nome della classe deve essere delimitato da parentesi quadre ([]) o virgolette ("").If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). Il metodo specificato deve essere un metodo statico della classe.The specified method must be a static method of the class.

Per impostazione predefinita, SQL ServerSQL Server non può eseguire il codice CLR.By default, SQL ServerSQL Server cannot execute CLR code. È possibile creare, modificare e rilasciare gli oggetti di database che fanno riferimento a moduli CLR; Tuttavia, non è possibile eseguire questi riferimenti in SQL ServerSQL Server finché non si abilita il opzione clr enabled.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. Per abilitare l'opzione, usare sp_configure.To enable the option, use sp_configure.

Nota

Le procedure CLR non sono supportate in un database indipendente.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Indica l'esecuzione atomica di stored procedure.Indicates atomic stored procedure execution. Viene eseguito il commit delle modifiche o il rollback di tutte le modifiche tramite la generazione di un'eccezione.Changes are either committed or all of the changes rolled back by throwing an exception. Il blocco ATOMIC WITH è obbligatorio per le stored procedure compilate in modo nativo.The ATOMIC WITH block is required for natively compiled stored procedures.

Se la procedura esegue RETURN (in modo esplicito tramite l'istruzione RETURN o in modo implicito completando l'esecuzione), viene eseguito il commit del lavoro svolto dalla procedura.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. Se la procedura esegue THROW, viene eseguito il rollback del lavoro svolto dalla procedura.If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT è ON per impostazione predefinita in un blocco atomico e non può essere modificato.XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT specifica se in SQL ServerSQL Server viene eseguito automaticamente il rollback della transazione corrente quando un'istruzione Transact-SQLTransact-SQL genera un errore di run-time.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

Le opzioni SET seguenti sono sempre impostate su ON nel blocco ATOMIC e non possono essere modificate.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

Le opzioni SET non possono essere modificate nei blocchi ATOMIC.SET options cannot be changed inside ATOMIC blocks. Le opzioni SET della sessione utente non vengono usate nell'ambito delle stored procedure compilate in modo nativo.The SET options in the user session are not used in the scope of natively compiled stored procedures. Queste opzioni vengono fissate in fase di compilazione.These options are fixed at compile time.

Le operazioni BEGIN, ROLLBACK e COMMIT non possono essere usate in un blocco atomico.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

Esiste un solo blocco ATOMIC per stored procedure compilata in modo nativo, nell'ambito esterno della procedura.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. I blocchi non possono essere nidificati.The blocks cannot be nested. Per ulteriori informazioni sui blocchi atomici, vedere Natively Compiled Stored Procedures.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NON È NULLNULL | NOT NULL
Determina se i valori Null sono supportati in un parametro.Determines whether null values are allowed in a parameter. Il valore predefinito è NULL.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Indica che la procedura è compilata in modo nativo.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING ed EXECUTE AS possono essere specificati in qualsiasi ordine.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. Per ulteriori informazioni, vedere Natively Compiled Stored Procedures.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Assicura che le tabelle a cui si fa riferimento in una procedura non possano essere eliminate o modificate.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING è obbligatorio nelle stored procedure compilate in modo nativo.SCHEMABINDING is required in natively compiled stored procedures. (Per ulteriori informazioni, vedere Natively Compiled Stored Procedures.) Le restrizioni SCHEMABINDING sono uguali a quelle delle funzioni definite dall'utente.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. Per ulteriori informazioni, vedere la sezione SCHEMABINDING in CREATE FUNCTION ( Transact-SQL ) .For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'lingua'LANGUAGE = [N] 'language'
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Equivalente a SET LANGUAGE ( Transact-SQL ) opzione della sessione.Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'lingua' è obbligatorio.LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Obbligatorio per stored procedure compilate in modo nativo.Required for natively compiled stored procedures. Specifica il livello di isolamento della transazione della stored procedure.Specifies the transaction isolation level for the stored procedure. Sono disponibili le opzioni seguenti:The options are as follows:

Per ulteriori informazioni su queste opzioni, vedere SET TRANSACTION ISOLATION LEVEL ( Transact-SQL ) .For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
Specifica che le istruzioni non possono leggere i dati modificati da altre transazioni, ma di cui non è ancora stato eseguito il commit.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. Se un'altra transazione modifica i dati letti dalla transazione corrente, la transazione corrente.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
Specifica quanto segue:Specifies the following:

  • Le istruzioni non possono leggere dati modificati da altre transazioni ma di cui non è ancora stato eseguito il commit.Statements cannot read data that has been modified but not yet committed by other transactions.
  • Se un'altra transazione modifica i dati letti dalla transazione corrente, la transazione corrente.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • Se un'altra transazione inserisce nuove righe con valori di chiave che rientrerebbero nell'intervallo di chiavi lette da qualsiasi istruzione nella transazione corrente, la transazione corrente.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
Specifica che i dati letti da qualsiasi istruzione in una transazione sono la versione consistente dal punto di dati presenti all'inizio della transazione.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numeroDATEFIRST = number
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Specifica il primo giorno della settimana come numero compreso tra 1 e 7.Specifies the first day of the week to a number from 1 through 7. DATEFIRST è facoltativo.DATEFIRST is optional. Se viene omesso, l'impostazione viene desunta dalla lingua specificata.If it is not specified, the setting is inferred from the specified language.

Per ulteriori informazioni, vedere SET DATEFIRST ( Transact-SQL ) .For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatoDATEFORMAT = format
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Specifica l'ordine delle parti della data relative a mese, giorno e anno per l'interpretazione di stringhe di caratteri date, smalldatetime, datetime, datetime2 e datetimeoffset.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT è facoltativo.DATEFORMAT is optional. Se viene omesso, l'impostazione viene desunta dalla lingua specificata.If it is not specified, the setting is inferred from the specified language.

Per ulteriori informazioni, vedere SET DATEFORMAT ( Transact-SQL ) .For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
Si applica a: SQL Server 2014SQL Server 2014 tramite SQL Server 2017SQL Server 2017 e Database SQL di AzureAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Database SQL di AzureAzure SQL Database.

Il commit delle transazioni di SQL ServerSQL Server può essere completamente durevole, ovvero l'impostazione predefinita di SQL Server, oppure con durabilità ritardata. SQL ServerSQL Server transaction commits can be either fully durable, the default, or delayed durable.

Per ulteriori informazioni, vedere controllo della durabilità delle transazioni.For more information, see Control Transaction Durability.

Esempi sempliciSimple Examples

Per iniziare, di seguito sono riportati due esempi rapidi:To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB;Restituisce il nome del database corrente.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
È possibile eseguire il wrapping tale istruzione in una stored procedure, ad esempio:You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

Chiamare la stored procedure con istruzione:EXEC What_DB_is_this;Call the store procedure with statement: EXEC What_DB_is_this;

Leggermente più complessa, consiste nel fornire un parametro di input per rendere la procedura più flessibile.Slightly more complex, is to provide an input parameter to make the procedure more flexible. Esempio:For example:

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

Specificare un numero di id di database quando si chiama la routine.Provide a database id number when you call the procedure. Ad esempio, EXEC What_DB_is_that 2; restituisce tempdb.For example, EXEC What_DB_is_that 2; returns tempdb.

Vedere esempi verso la fine di questo argomento per ulteriori esempi.See Examples towards the end of this topic for many more examples.

Procedure consigliateBest Practices

Sebbene non siano elencate tutte le procedure consigliate, questi suggerimenti possono migliorare le prestazioni della procedura.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Usare l'istruzione SET NOCOUNT ON come prima istruzione nel corpo della procedura,Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. ovvero posizionarla subito dopo la parola chiave AS.That is, place it just after the AS keyword. In questo modo vengono disabilitati i messaggi restituiti al client da SQL ServerSQL Server dopo l'esecuzione delle istruzioni SELECT, INSERT, UPDATE, MERGE e DELETE.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. Le prestazioni generali del database e dell'applicazione vengono migliorate eliminando questo overhead di rete.Overall performance of the database and application is improved by eliminating this unnecessary network overhead. Per informazioni, vedere SET NOCOUNT ( Transact-SQL ) .For information, see SET NOCOUNT (Transact-SQL).

  • Usare i nomi degli schemi quando si creano oggetti di database nella procedura o vi si fa riferimento.Use schema names when creating or referencing database objects in the procedure. Richiede meno tempo di elaborazione il Motore di databaseDatabase Engine per risolvere i nomi di oggetto, se non dispone di più schemi di ricerca.It takes less processing time for the Motore di databaseDatabase Engine to resolve object names if it does not have to search multiple schemas. Impedisce inoltre l'autorizzazione e i problemi di accesso causati da schema predefinito dell'utente viene assegnato quando vengono creati oggetti senza specificare lo schema.It also prevents permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema.

  • Evitare l'esecuzione del wrapping di funzioni attorno alle colonne specificate nelle clausole WHERE e JOIN.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. In tal modo le colonne vengono rese non deterministiche e si evita l'utilizzo di indici in Query Processor.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Evitare l'utilizzo di funzioni scalari nelle istruzioni SELECT che restituiscono molte righe di dati.Avoid using scalar functions in SELECT statements that return many rows of data. Poiché la funzione scalare deve essere applicata a ogni riga, il comportamento risultante assomiglia all'elaborazione basata su righe e ciò comporta un peggioramento delle prestazioni.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Evitare l'utilizzo di SELECT *.Avoid the use of SELECT *. Specificare invece i nomi delle colonne necessarie.Instead, specify the required column names. In questo modo è possibile evitare alcuni errori del Motore di databaseDatabase Engine che causano l'arresto dell'esecuzione della procedura.This can prevent some Motore di databaseDatabase Engine errors that stop procedure execution. Ad esempio, un SELECT * istruzione che restituisce dati da una tabella di 12 colonne e quindi tali dati vengono inseriti in una tabella temporanea di 12 colonne viene completata finché il numero o viene modificato l'ordine delle colonne delle tabelle.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • Evitare l'elaborazione o la restituzione di troppi dati.Avoid processing or returning too much data. Non appena possibile, restringere i risultati nel codice della procedura in modo che le operazioni successive effettuate dalla procedura vengano eseguite usando il set di dati più piccolo possibile.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Inviare solo i dati essenziali all'applicazione client.Send just the essential data to the client application. L'operazione è più efficace dell'invio di dati aggiuntivi nella rete, nonché dell'imposizione all'applicazione client di usare set di risultati inutilmente grandi.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Utilizzare le transazioni esplicite tramite BEGIN/COMMIT della transazione e mantenere le transazioni più brevi possibili.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Transazioni lunghe implicano un blocco dei record più lungo e un rischio maggiore di deadlock.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • Per la gestione degli errori all'interno di una procedura usare la funzionalità TRY…CATCH di Transact-SQLTransact-SQLUse the Transact-SQLTransact-SQL TRY…CATCH feature for error handling inside a procedure. che consente di incapsulare un blocco intero di istruzioni Transact-SQLTransact-SQL.TRY…CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. In questo modo vengono garantiti un minor overhead delle prestazioni e una segnalazione errori più precisa con un utilizzo inferiore della programmazione.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • Usare la parola chiave DEFAULT in tutte le colonne della tabella a cui viene fatto riferimento dalle istruzioni Transact-SQLTransact-SQL CREATE TABLE o ALTER TABLE presenti nel corpo della procedura.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. In questo modo si impedisce il passaggio di NULL alle colonne che non ammettono valori null.This prevents passing NULL to columns that do not allow null values.

  • Usare NULL o NOT NULL per ogni colonna di una tabella temporanea.Use NULL or NOT NULL for each column in a temporary table. Le opzioni ANSI_DFLT_ON e ANSI_DFLT_OFF consentono di controllare la modalità di assegnazione dell'attributo NULL o NOT NULL alle colonne da parte del Motore di databaseDatabase Engine quando tale attributo non è specificato in un'istruzione CREATE TABLE o ALTER TABLE.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Motore di databaseDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. Se in una connessione viene eseguita una procedura con opzioni impostate in modo diverso rispetto alla connessione in cui la procedura è stata creata, è possibile che il supporto di valori Null e il funzionamento delle colonne della tabella creata per la seconda connessione siano diversi.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. Se l'attributo NULL o NOT NULL viene dichiarato in modo esplicito per ogni colonna, le tabelle temporanee vengono create con lo stesso supporto di valori Null per tutte le connessioni in cui viene eseguita la procedura.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Usare le istruzioni di modifica che consentono di convertire i valori Null e in cui è inclusa la logica che permette di eliminare le righe con valori Null dalle query.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Tenere presente che in Transact-SQLTransact-SQL, NULL non è vuota o valore "nothing".Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. Si tratta di un segnaposto per un valore sconosciuto e può causare un comportamento imprevisto, soprattutto quando si eseguono query per set di risultati o si usano le funzioni di aggregazione.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Usare l'operatore UNION ALL invece dell'operatore UNION oppure OR, a meno che non siano necessari valori distinct.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. L'operatore UNION ALL richiede un minor overhead di elaborazione poiché i duplicati non vengono esclusi dal set di risultati.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

Osservazioni generaliGeneral Remarks

Non è prevista una dimensione massima predefinita per una procedura.There is no predefined maximum size of a procedure.

Le variabili specificate nella procedura possono essere definite dall'utente o variabili di sistema, ad esempio @@SPID.Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

Alla prima esecuzione, la procedura viene compilata in modo da determinare un piano di accesso ottimale per il recupero dei dati.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. Se il piano generato rimane archiviato nell'apposita cache del Motore di databaseDatabase Engine, può essere riutilizzato nelle successive esecuzioni della procedura.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Motore di databaseDatabase Engine.

È possibile eseguire automaticamente una o più procedure all'avvio di SQL ServerSQL Server.One or more procedures can execute automatically when SQL ServerSQL Server starts. Le procedure devono essere create dall'amministratore di sistema nel master database ed eseguite le sysadmin ruolo predefinito del server come un processo in background.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. In queste procedure non è possibile usare parametri di input o output.The procedures cannot have any input or output parameters. Per ulteriori informazioni, vedere eseguire una Stored Procedure.For more information, see Execute a Stored Procedure.

Le procedure vengono nidificate quando una procedura consente la chiamata di un'altra o l'esecuzione di codice gestito facendo riferimento a una routine, un tipo o una funzione di aggregazione CLR.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. È possibile nidificare fino a 32 livelli di procedure e riferimenti a codice gestito.Procedures and managed code references can be nested up to 32 levels. Il livello di nidificazione viene incrementato di un'unità quando viene avviata l'esecuzione della procedura o del riferimento al codice gestito chiamato e viene ridotto di un'unità quando ne viene completata l'esecuzione.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. I metodi richiamati all'interno del codice gestito non vengono inclusi nel limite del livello di nidificazione.Methods invoked from within the managed code do not count against the nesting level limit. Tuttavia, quando tramite una stored procedure CLR vengono eseguite operazioni di accesso ai dati tramite il provider gestito SQL Server, nel passaggio dal codice gestito a SQL viene aggiunto un ulteriore livello di nidificazione.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

Il tentativo di superare il livello di nidificazione massimo causa l'esito negativo dell'intera catena di chiamata.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. È possibile utilizzare il @@NESTLEVEL funzione per restituire il livello di nidificazione dell'esecuzione della stored procedure corrente.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

InteroperabilitàInteroperability

Quando viene creata o modificata una procedura Motore di databaseDatabase Engine, nel Transact-SQLTransact-SQL vengono salvate le impostazioni di entrambe le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS.The Motore di databaseDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. Queste impostazioni originali vengono usate quando viene eseguita la procedura.These original settings are used when the procedure is executed. Pertanto, le impostazioni di sessione del client per le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS vengono ignorate durante l'esecuzione della procedura.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Altre opzioni SET, ad esempio SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, non vengono salvate quando viene creata o modificata una procedura.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. Se la logica della procedura dipende da una particolare impostazione, includere un'istruzione SET all'inizio della procedura per garantire l'utilizzo dell'impostazione adeguata.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. Quando un'istruzione SET viene eseguita da una procedura, l'impostazione rimane attiva solo fino al termine dell'esecuzione della procedura.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. L'impostazione viene quindi ripristinata al valore assegnato alla procedura quando è stata chiamata.The setting is then restored to the value the procedure had when it was called. In tal modo nei singoli client è possibile impostare le opzioni desiderate senza influire sulla logica della procedura.This enables individual clients to set the options they want without affecting the logic of the procedure.

In una procedura è possibile specificare qualsiasi istruzione SET, ad eccezione di SET SHOWPLAN_TEXT e SET SHOWPLAN_ALL.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. Queste devono essere le uniche istruzioni in un batch.These must be the only statements in the batch. L'opzione SET scelta rimane attiva durante l'esecuzione della procedura, dopodiché viene ripristinata l'impostazione precedente.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

Nota

SET_ANSI_WARNINGS non viene applicata quando vengono passati parametri in una procedura, in una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. Ad esempio, se una variabile viene definita come char(3), quindi impostare un valore maggiore di tre caratteri, i dati vengono troncati alla dimensione definita e l'inserimento o istruzione di aggiornamento ha esito positivo.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Limitazioni e restrizioniLimitations and Restrictions

L'istruzione CREATE PROCEDURE non può essere usata in combinazione con altre istruzioni Transact-SQLTransact-SQL all'interno di un singolo batch.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

Le istruzioni seguenti non possono essere usate in un qualsiasi punto del corpo di una stored procedure.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE o ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE o ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE o ALTER VIEWCREATE or ALTER VIEW Utilizzare database_nameUSE database_name
CREATE o ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

Una procedura può fare riferimento a tabelle che non esistono ancora.A procedure can reference tables that do not yet exist. In fase di creazione viene eseguito solo un controllo della sintassi.At creation time, only syntax checking is performed. La procedura non viene compilata fino alla prima esecuzioneThe procedure is not compiled until it is executed for the first time. ed è solo durante la compilazione che vengono risolti tutti gli oggetti a cui viene fatto riferimento nella procedura.Only during compilation are all objects referenced in the procedure resolved. Pertanto, una procedura con sintassi corretta che fa riferimento a tabelle che non esistono possibile creata correttamente. Tuttavia, la procedura ha esito negativo in fase di esecuzione se le tabelle di riferimento non esiste.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

Non è possibile specificare un nome di funzione come valore predefinito di un parametro o come valore passato a un parametro durante l'esecuzione di una procedura.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. Tuttavia, è possibile passare una funzione come variabile, come illustrato nell'esempio seguente.However, you can pass a function as a variable as shown in the following example.

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

Se la procedura consente di apportare modifiche in un'istanza remota di SQL ServerSQL Server, non è possibile eseguire il rollback delle modifiche.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. Le procedure remote non partecipano alle transazioni.Remote procedures do not take part in transactions.

Affinché il Motore di databaseDatabase Engine faccia riferimento al metodo corretto quando viene eseguito l'overload in .NET Framework, il metodo specificato nella clausola EXTERNAL NAME deve soddisfare i requisiti seguenti:For the Motore di databaseDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • Essere dichiarato come metodo statico.Be declared as a static method.

  • Ricevere lo stesso numero di parametri della procedura.Receive the same number of parameters as the number of parameters of the procedure.

  • Usare tipi di parametro compatibili con i tipi di dati dei parametri corrispondenti della procedura di SQL ServerSQL Server.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. Per informazioni sulla corrispondenza SQL ServerSQL Server tipi di dati per il .NET Framework.NET Framework tipi di dati, vedere Mapping dei dati di parametro CLR.For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

MetadatiMetadata

Nella tabella seguente sono elencate le viste del catalogo e le DMV utilizzabili per restituire informazioni sulle stored procedure.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

VisualizzaView DescriptionDescription
sys.sql_modulessys.sql_modules Viene restituita la definizione di una procedura Transact-SQLTransact-SQL.Returns the definition of a Transact-SQLTransact-SQL procedure. Il testo di una procedura creata con l'opzione ENCRYPTION non può essere visualizzato utilizzando il Sys. sql_modules vista del catalogo.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules Vengono restituite informazioni su una procedura CLR.Returns information about a CLR procedure.
sys.parameterssys.parameters Vengono restituite informazioni sui parametri definiti in una procedura.Returns information about the parameters that are defined in a procedure
Sys. sql_expression_dependencies Sys.dm sql_referenced_entities Sys.dm sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities Vengono restituiti gli oggetti a cui una procedura fa riferimento.Returns the objects that are referenced by a procedure.

Per stimare le dimensioni di una procedura compilata, usare i seguenti contatori di Performance Monitor.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

Nome dell'oggetto di Performance MonitorPerformance Monitor object name Nome del contatore di Performance MonitorPerformance Monitor Counter name
SQLServer: Plan Cache ObjectSQLServer: Plan Cache Object Percentuale riscontri cacheCache Hit Ratio
Pagine cacheCache Pages
Conteggio oggetti cacheCache Object Counts

* Questi contatori sono disponibili per diverse categorie di oggetti della cache incluse ad hoc Transact-SQLTransact-SQL, preparato Transact-SQLTransact-SQL, procedure, trigger e così via.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. Per ulteriori informazioni, vedere SQL Server, pianificare oggetto Cache.For more information, see SQL Server, Plan Cache Object.

SecuritySecurity

PermissionsPermissions

Richiede CREATE PROCEDURE autorizzazione per il database e ALTER autorizzazione per lo schema in cui viene creata la procedura o richiede l'appartenenza di db_ddladmin ruolo predefinito del database.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

Per le stored procedure CLR, è necessario essere proprietari dell'assembly a cui fa riferimento nella clausola EXTERNAL NAME o riferimenti autorizzazione tale assembly.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREARE PROCEDURE e tabelle con ottimizzazione per la memoriaCREATE PROCEDURE and Memory-Optimized Tables

Le tabelle con ottimizzazione per la memoria è possibile accedere tramite le stored procedure tradizionali e compilate in modo nativo.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. Procedure native sono nella maggior parte dei casi il modo più efficiente.Native procedures are in most cases the more efficient way. Per ulteriori informazioni, vedere Natively Compiled Stored Procedures.For more information, see Natively Compiled Stored Procedures.

L'esempio seguente viene illustrato come creare una stored procedure compilata in modo nativo che accede a una tabella con ottimizzazione per la memoria dbo.Departments:The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  

  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO  

Una procedura creata senza NATIVE_COMPILATION non può essere modificata in una stored procedure compilata in modo nativo.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

Per informazioni sulla programmabilità nelle stored procedure compilate in modo nativo, supportate della superficie di attacco di query e sugli operatori, vedere funzionalità supportate per moduli T-SQL compilati in modo nativo.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

EsempiExamples

CategoryCategory Elementi di sintassi inclusiFeatured syntax elements
Sintassi di baseBasic Syntax CREATE PROCEDURECREATE PROCEDURE
Passaggio di parametriPassing parameters @parameter
  • = predefinito   • = default
  • L'OUTPUT   • OUTPUT
  tipo di parametro con valori di tabella •   • table-valued parameter type
  • CURSOR VARYING.   • CURSOR VARYING
Modifica dei dati tramite una stored procedureModifying data by using a stored procedure UPDATEUPDATE
Gestione degli erroriError Handling TRY…CATCHTRY…CATCH
Offuscamento della definizione di procedureObfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
Forzare la ricompilazione di stored ProcedureForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
Impostazione del contesto di sicurezzaSetting the Security Context EXECUTE ASEXECUTE AS

Sintassi di baseBasic Syntax

Negli esempi contenuti in questa sezione vengono illustrate le funzionalità di base dell'istruzione CREATE PROCEDURE tramite la sintassi minima necessaria.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. Creazione di una procedura Transact-SQL sempliceCreating a simple Transact-SQL procedure

Nell'esempio seguente viene creata una stored procedure tramite cui vengono restituiti tutti i dipendenti (per cui vengono indicati il nome e il cognome), le relative posizioni e i nomi dei reparti di appartenenza da una vista nel database AdventureWorks2012AdventureWorks2012.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. In questa procedura non viene usato alcun parametro.This procedure does not use any parameters. Nell'esempio vengono quindi illustrati tre metodi di esecuzione della procedura.The example then demonstrates three methods of executing the procedure.

CREATE PROCEDURE HumanResources.uspGetAllEmployees  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment;  
GO  

SELECT * FROM HumanResources.vEmployeeDepartment;  

Il uspGetEmployees procedure può essere eseguita nei modi seguenti:The uspGetEmployees procedure can be executed in the following ways:

EXECUTE HumanResources.uspGetAllEmployees;  
GO  
-- Or  
EXEC HumanResources.uspGetAllEmployees;  
GO  
-- Or, if this procedure is the first statement within a batch:  
HumanResources.uspGetAllEmployees;  

B.B. Restituzione di più di un set di risultatiReturning more than one result set

Tramite la procedura seguente vengono restituiti due set di risultati.The following procedure returns two result sets.

CREATE PROCEDURE dbo.uspMultipleResults   
AS  
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;  
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;  
GO  

C.C. Creazione di una stored procedure CLRCreating a CLR stored procedure

Nell'esempio seguente viene creato il GetPhotoFromDB routine che fa riferimento il GetPhotoFromDB metodo il LargeObjectBinary classe nel HandlingLOBUsingCLR assembly.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Prima della creazione, la procedura di HandlingLOBUsingCLR assembly viene registrato nel database locale.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

Si applica a: SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017, Database SQLSQL Database (se si utilizza un assembly creato da assembly_bits.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Database SQLSQL Database (if using an assembly created from assembly_bits.

CREATE ASSEMBLY HandlingLOBUsingCLR  
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';  
GO  
CREATE PROCEDURE dbo.GetPhotoFromDB  
(  
    @ProductPhotoID int,  
    @CurrentDirectory nvarchar(1024),  
    @FileName nvarchar(1024)  
)  
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;  
GO  

Passaggio di parametriPassing Parameters

Negli esempi di questa sezione viene illustrato l'utilizzo dei parametri di input e di output per il passaggio di valori a e da una stored procedure.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. Creazione di una procedura con parametri di inputCreating a procedure with input parameters

Nell'esempio seguente viene creata una stored procedure tramite cui vengono restituite informazioni per un dipendente specifico passando i valori relativi al nome e al cognome del dipendente.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. In questa procedura vengono accettate solo corrispondenze esatte per i parametri passati.This procedure accepts only exact matches for the parameters passed.

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetEmployees;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployees   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName = @FirstName AND LastName = @LastName;  
GO  

Il uspGetEmployees procedure può essere eseguita nei modi seguenti:The uspGetEmployees procedure can be executed in the following ways:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
-- Or  
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO  
-- Or, if this procedure is the first statement within a batch:  
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';  

E.E. Utilizzo di una procedura con parametri di caratteri jollyUsing a procedure with wildcard parameters

Nell'esempio seguente viene creata una stored procedure tramite cui vengono restituite informazioni per i dipendenti passando valori completi o parziali relativi al nome e al cognome dei dipendenti.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. Schema di questa procedura corrisponde ai parametri passati oppure, se non specificato, utilizza i valori predefiniti (cognomi che iniziano con la lettera D).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).

IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetEmployees2;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployees2   
    @LastName nvarchar(50) = N'D%',   
    @FirstName nvarchar(50) = N'%'  
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;  

Il uspGetEmployees2 procedura può essere eseguita in molte combinazioni.The uspGetEmployees2 procedure can be executed in many combinations. Di seguito sono riportate solo alcune delle combinazioni possibili.Only a few possible combinations are shown here.

EXECUTE HumanResources.uspGetEmployees2;  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';  

F.F. Utilizzo di parametri OUTPUTUsing OUTPUT parameters

Nell'esempio seguente viene creata la procedura uspGetList.The following example creates the uspGetList procedure. che restituisce un elenco di prodotti il cui prezzo non supera un determinato importo.This procedures returns a list of products that have prices that do not exceed a specified amount. In questo 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, un batch o più istruzioni Transact-SQLTransact-SQL di accedere a un valore impostato durante l'esecuzione della procedura.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 uspGetList per restituire un elenco dei prodotti di Adventure WorksAdventure Works (biciclette) con un prezzo inferiore a $700.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. Il OUTPUT parametri @Cost e @ComparePrices vengono utilizzati con il linguaggio del flusso di controllo per restituire un messaggio nel messaggi finestra.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 sia quando viene creata la procedura che quando viene usata la variabile.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. Il nome del parametro e il nome di variabile non è necessario corrispondenti. Tuttavia, il tipo di dati e la posizione del parametro devono corrispondere, a meno che non @ListPrice = variabile viene utilizzato.The parameter name and variable name do not have to match; 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.

G.G. Utilizzo di un parametro con valori di tabellaUsing a Table-Valued Parameter

Nell'esempio seguente viene usato un tipo di parametro con valori di tabella per inserire più righe in una tabella.The following example uses a table-valued parameter type to insert multiple rows into a table. Nell'esempio viene creato il tipo di parametro, viene dichiarata una variabile di tabella per farvi riferimento, viene riempito l'elenco di parametri e, successivamente, vengono passati i valori a una stored procedure,The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. usati da quest'ultima per inserire più righe in una tabella.The stored procedure uses the values to insert multiple rows into a table.

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  

/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
H.H. Utilizzo di un parametro OUTPUT di tipo cursoreUsing an OUTPUT cursor parameter

Nell'esempio seguente viene usato il parametro OUTPUT di tipo cursore per passare nuovamente al batch, alla procedura o al trigger chiamante un cursore locale rispetto a una procedura.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

Creare innanzitutto la procedura che consente di dichiarare e, successivamente, di aprire un cursore nella tabella Currency:First, create the procedure that declares and then opens a cursor on the Currency table:

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO  

Eseguire quindi un batch che consente di dichiarare una variabile locale di cursore, di eseguire la procedura per assegnare il cursore alla variabile locale e, successivamente, di recuperare le righe dal cursore.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO  

Modifica dei dati tramite una Stored ProcedureModifying Data by using a Stored Procedure

Negli esempi contenuti in questa sezione viene illustrato come inserire o modificare i dati di tabelle o viste includendo un'istruzione DML (Data Manipulation Language) nella definizione della procedura.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. Utilizzo di UPDATE in una stored procedureUsing UPDATE in a stored procedure

Nell'esempio seguente viene usata un'istruzione UPDATE in una stored procedure.The following example uses an UPDATE statement in a stored procedure. Per la stored procedure sono previsti un unico parametro di input @NewHours e un unico parametro di output @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. Il @NewHours valore del parametro viene utilizzato nell'istruzione UPDATE per aggiornare la colonna VacationHours nella tabella HumanResources.Employee.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. Il parametro di output @RowCount viene usato per restituire il numero di righe interessate a una variabile locale.The @RowCount output parameter is used to return the number of rows affected to a local variable. Un'espressione CASE viene usata nella clausola SET per determinare in modo condizionale il valore impostato per VacationHours.A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. Quando un dipendente percepisce una paga oraria (SalariedFlag = 0), VacationHours viene impostato sul numero corrente di ore più il valore specificato in @NewHours. In caso contrario, VacationHours viene impostato sul valore specificato in @NewHours.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours smallint  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  

EXEC HumanResources.Update_VacationHours 40;  

Gestione degli erroriError Handling

Negli esempi contenuti in questa sezione vengono illustrati i metodi per gestire gli errori che potrebbero verificarsi durante l'esecuzione della stored procedure.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. Utilizzo di TRY…CATCHUsing TRY…CATCH

Nell'esempio seguente viene illustrato l'utilizzo di un costrutto TRY…CATCH per restituire informazioni sugli errori rilevati durante l'esecuzione di una stored procedure.The following example using the TRY…CATCH construct to return error information caught during the execution of a stored procedure.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  
SET NOCOUNT ON;  
BEGIN TRY  
   BEGIN TRANSACTION   
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  

GO  
EXEC Production.uspDeleteWorkOrder 13;  

/* Intentionally generate an error by reversing the order in which rows 
   are deleted from the parent and child tables. This change does not 
   cause an error when the procedure definition is altered, but produces 
   an error when the procedure is executed.  
*/  
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  

BEGIN TRY  
   BEGIN TRANSACTION   
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT TRANSACTION  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK TRANSACTION  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  
GO  
-- Execute the altered procedure.  
EXEC Production.uspDeleteWorkOrder 15;  

DROP PROCEDURE Production.uspDeleteWorkOrder;  

Offuscamento della definizione di ProcedureObfuscating the Procedure Definition

Negli esempi contenuti in questa sezione viene illustrato come offuscare la definizione della stored procedure.Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. Utilizzo dell'opzione WITH ENCRYPTIONUsing the WITH ENCRYPTION option

Nell'esempio seguente viene creata la procedura HumanResources.uspEncryptThis.The following example creates the HumanResources.uspEncryptThis procedure.

Si applica a: SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017, Database SQL.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

Il WITH ENCRYPTION opzione consente di offuscare la definizione della routine quando l'esecuzione di query nel catalogo di sistema o tramite i metadati funzioni, come illustrato negli esempi seguenti.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Run sp_helptext:Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

Set di risultati:Here is the result set.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Eseguire una query direttamente il sys.sql_modules vista del catalogo:Directly query the sys.sql_modules catalog view:

SELECT definition FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');  

Set di risultati:Here is the result set.

definition  
--------------------------------  
NULL  

Forzare la ricompilazione di stored ProcedureForcing the Procedure to Recompile

Negli esempi contenuti in questa sezione viene usata la clausola WITH RECOMPILE per forzare la ricompilazione della procedura a ogni esecuzione.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. Utilizzo dell'opzione WITH RECOMPILEUsing the WITH RECOMPILE option

Il WITH RECOMPILE clausola è utile quando i parametri specificati per la procedura non sono tipici e quando un nuovo piano di esecuzione non deve essere memorizzato nella cache o archiviato in memoria.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.uspProductByVendor;  
GO  
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
WITH RECOMPILE  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
    FROM Purchasing.Vendor AS v   
    JOIN Purchasing.ProductVendor AS pv   
      ON v.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;  

Impostazione del contesto di sicurezzaSetting the Security Context

Negli esempi contenuti in questa sezione viene usata la clausola EXECUTE AS per impostare il contesto di sicurezza in cui viene eseguita la stored procedure.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. Utilizzo della clausola EXECUTE ASUsing the EXECUTE AS clause

Nell'esempio seguente viene illustrato l'utilizzo di EXECUTE AS clausola per specificare il contesto di sicurezza in cui è possibile eseguire una stored procedure.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. Nell'esempio, l'opzione CALLER specifica che la procedura può essere eseguita nel contesto dell'utente che lo chiama.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

CREATE PROCEDURE Purchasing.uspVendorAllInfo  
WITH EXECUTE AS CALLER  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS Vendor, p.Name AS 'Product name',   
      v.CreditRating AS 'Rating',   
      v.ActiveFlag AS Availability  
    FROM Purchasing.Vendor v   
    INNER JOIN Purchasing.ProductVendor pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product p  
      ON pv.ProductID = p.ProductID   
    ORDER BY v.Name ASC;  
GO  

N.N. Creazione di set di autorizzazioni personalizzateCreating custom permission sets

Nell'esempio seguente viene usata la clausola EXECUTE AS per creare autorizzazioni personalizzate per un'operazione sul database.The following example uses EXECUTE AS to create custom permissions for a database operation. Per alcune operazioni, ad esempio TRUNCATE TABLE, non è possibile concedere le autorizzazioni.Some operations such as TRUNCATE TABLE, do not have grantable permissions. Incorporando l'istruzione TRUNCATE TABLE in una stored procedure e specificando che tale procedura venga eseguita come un utente che dispone di autorizzazioni per la modifica della tabella è possibile estendere le autorizzazioni per il troncamento della tabella all'utente al quale si concedono le autorizzazioni EXECUTE sulla procedura.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

CREATE PROCEDURE dbo.TruncateMyTable  
WITH EXECUTE AS SELF  
AS TRUNCATE TABLE MyDB..MyTable;  

Esempi: Azure SQL Data WarehouseAzure SQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL Data WarehouseAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

O.O. Creare una Stored Procedure che esegue un'istruzione SELECTCreate a Stored Procedure that runs a SELECT statement

Questo esempio mostra la sintassi di base per la creazione e l'esecuzione di una routine.This example shows the basic syntax for creating and running a procedure. Quando si esegue un batch, CREATE PROCEDURE deve essere la prima istruzione.When running a batch, CREATE PROCEDURE must be the first statement. Ad esempio, per creare la seguente stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, prima di impostare il contesto del database e quindi eseguire l'istruzione CREATE PROCEDURE.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  

--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  

--Show 10 Top Resellers  
EXEC Get10TopResellers;  

Vedere ancheSee Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
Il controllo di flusso Language ( Transact-SQL ) Control-of-Flow Language (Transact-SQL)
Cursori Cursors
Tipi di dati (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE ( Transact-SQL ) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
ESEGUIRE AS ( Transact-SQL ) EXECUTE AS (Transact-SQL)
Stored procedure (Motore di database) Stored Procedures (Database Engine)
sp_procoption ( Transact-SQL ) sp_procoption (Transact-SQL)
sp_recompile ( Transact-SQL ) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
numbered_procedures ( Transact-SQL ) sys.numbered_procedures (Transact-SQL)
numbered_procedure_parameters ( Transact-SQL ) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
Creazione di una stored procedure Create a Stored Procedure
Utilizzare i valori di tabella parametri ( motore di Database ) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)