Stored procedure (Motore di database)Stored Procedures (Database Engine)

Una stored procedure in SQL ServerSQL Server è un gruppo di una o più istruzioni Transact-SQLTransact-SQL oppure un riferimento a un metodo CLR (Common Runtime Language) di MicrosoftMicrosoft .NET Framework.NET Framework .A stored procedure in SQL ServerSQL Server is a group of one or more Transact-SQLTransact-SQL statements or a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method. Le stored procedure assomigliano ai costrutti di altri linguaggi di programmazione perché possono essere utilizzate per:Procedures resemble constructs in other programming languages because they can:

  • Accettare parametri di input e restituire più valori sotto forma di parametri di output al programma che esegue la chiamata.Accept input parameters and return multiple values in the form of output parameters to the calling program.

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

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

Vantaggi dell'utilizzo delle stored procedureBenefits of Using Stored Procedures

Nell'elenco seguente vengono descritti alcuni vantaggi dell'utilizzo di stored procedure.The following list describes some benefits of using procedures.

Riduzione del traffico di rete server/clientReduced server/client network traffic
I comandi in una stored procedure vengono eseguiti come un solo batch di codice.The commands in a procedure are executed as a single batch of code. In questo modo è possibile ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la stored procedure viene inviata attraverso la rete.This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Senza l'incapsulamento del codice consentito dalla stored procedure, la rete viene attraversata da ogni singola riga di codice.Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

Miglioramento della sicurezzaStronger security
Tramite una stored procedure, più utenti e programmi client sono in grado di eseguire operazioni su oggetti di database sottostanti, anche se gli utenti e i programmi non dispongono di autorizzazioni dirette su tali oggetti sottostanti.Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. La stored procedure consente di controllare quali processi e attività vengono eseguiti e di proteggere gli oggetti di database sottostanti.The procedure controls what processes and activities are performed and protects the underlying database objects. In questo modo si elimina la necessità di concedere autorizzazioni a livello di singolo oggetto, semplificando i livelli di sicurezza.This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

È possibile specificare la clausola EXECUTE AS nell'istruzione CREATE PROCEDURE per consentire la rappresentazione di un altro utente o consentire a utenti e applicazioni di eseguire alcune attività nel database senza avere bisogno di autorizzazioni dirette per gli oggetti e i comandi sottostanti.The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. Ad esempio per alcune azioni, come TRUNCATE TABLE non sono disponibili autorizzazioni.For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. Per eseguire TRUNCATE TABLE è necessario che l'utente disponga dell'autorizzazione ALTER per la tabella specificata.To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Concedere a un utente l'autorizzazione ALTER su una tabella non è consigliabile, perché l'utente disporrebbe di autorizzazioni ben superiori alla semplice possibilità di troncare la tabella.Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. Incorporando l'istruzione TRUNCATE TABLE in un modulo e specificando che tale modulo venga eseguito 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 concede l'autorizzazione EXECUTE sul modulo.By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who 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 module.

In caso di chiamata a una stored procedure attraverso la rete, solo la chiamata per eseguire la stored procedure è visibile.When calling a procedure over the network, only the call to execute the procedure is visible. Pertanto, gli utenti malintenzionati non possono visualizzare i nomi di oggetti di database e tabelle, incorporare istruzioni Transact-SQLTransact-SQL personalizzate o cercare dati critici.Therefore, malicious users cannot see table and database object names, embed Transact-SQLTransact-SQL statements of their own, or search for critical data.

L'utilizzo dei parametri della stored procedure aiuta a proteggersi da attacchi SQL injection.Using procedure parameters helps guard against SQL injection attacks. Dal momento che l'input del parametro viene trattato come un valore letterale e non come codice eseguibile, è più difficile che un pirata informatico riesca a inserire un comando nelle istruzioni Transact-SQLTransact-SQL all'interno della stored procedure compromettendo la sicurezza.Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQLTransact-SQL statement(s) inside the procedure and compromise security.

È possibile crittografare le stored procedure, nascondendo il codice sorgente.Procedures can be encrypted, helping to obfuscate the source code. Per altre informazioni, vedere Crittografia di SQL Server.For more information, see SQL Server Encryption.

Riutilizzo del codiceReuse of code
Il codice di operazioni ripetitive sul database risulta assolutamente appropriato ai fini dell'incapsulamento nelle stored procedure.The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. In questo modo si elimina la necessità di riscrivere più volte lo stesso codice, si riducono le incoerenze al suo interno e se ne consentono l'accesso e l'esecuzione da parte di qualsiasi utente o applicazione che disponga delle autorizzazioni necessarie.This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

Semplificazione della manutenzioneEasier maintenance
Quando si effettua la chiamata delle stored procedure tramite le applicazioni client e si mantengono le operazioni nel database solo nel livello dati, in caso di modifiche nel database sottostante è necessario aggiornare unicamente le stored procedure.When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. Si mantiene separato il livello applicazione senza che venga interessato dalle modifiche a layout del database, relazioni o processi.The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

Miglioramento delle prestazioniImproved performance
Per impostazione predefinita, una stored procedure viene compilata solo alla prima esecuzione e si crea un piano di esecuzione da riutilizzare nelle esecuzioni successive.By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Dal momento che non è necessaria la creazione di un nuovo piano da parte del sistema di elaborazione delle query, l'elaborazione della stored procedure richiede generalmente un tempo minore.Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

Se c'è stata modifica significativa alle tabelle o ai dati a cui fa riferimento la stored procedure, è possibile che il piano precompilato comporti in realtà un'esecuzione più lenta della stored procedure.If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In questo caso, la ricompilazione della stored procedure e la forzatura di un nuovo piano di esecuzione possono migliorare le prestazioni.In this case, recompiling the procedure and forcing a new execution plan can improve performance.

Tipi di stored procedureTypes of Stored Procedures

Definita dall'utenteUser-defined
È possibile creare una stored procedure definita dall'utente in un database definito dall'utente o in tutti i database del sistema a eccezione del database delle risorse.A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. La stored procedure può essere sviluppata in Transact-SQLTransact-SQL o come un riferimento a un metodo CLR (Common Runtime Language) di MicrosoftMicrosoft .NET Framework.NET Framework.The procedure can be developed in either Transact-SQLTransact-SQL or as a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method.

TemporaneaTemporary
Le stored procedure temporanee sono un tipo di stored procedure definite dall'utente.Temporary procedures are a form of user-defined procedures. Sono simili alle stored procedure permanenti, ma vengono archiviate in tempdb.The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb. Esistono due tipi di stored procedure temporanee:locali e globali.There are two types of temporary procedures: local and global. I due tipi differiscono per i nomi, la visibilità e la disponibilità.They differ from each other in their names, their visibility, and their availability. Le stored procedure temporanee locali contengono un solo simbolo di cancelletto (#) come primo carattere del nome, sono visibili solo durante la connessione utente corrente e vengono eliminate alla chiusura della connessione.Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed. Le stored procedure temporanee globali contengono due simboli di cancelletto (##) come primi caratteri del nome, sono visibili per tutti gli utenti in seguito alla creazione e vengono eliminate al termine dell'ultima sessione che utilizza la stored procedure.Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

SistemaSystem
Le stored procedure di sistema sono incluse in SQL ServerSQL Server.System procedures are included with SQL ServerSQL Server. Sono archiviate fisicamente nel database delle risorse , interno e nascosto e livello logico compaiono nello schema sys di tutti i database di sistema e di quelli definiti dall'utente.They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. Inoltre, il database msdb contiene anche stored procedure di sistema nello schema dbo che vengono utilizzate per la pianificazione di avvisi e processi.In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Dal momento che le stored procedure di sistema iniziano con il prefisso sp_, si consiglia di non utilizzare questo prefisso per l'assegnazione di un nome alle procedure definite dall'utente.Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. Per un elenco completo delle stored procedure di sistema, vedere Stored procedure di sistema (Transact-SQL)For a complete list of system procedures, see System Stored Procedures (Transact-SQL)

SQL ServerSQL Server supporta le stored procedure di sistema che forniscono ai programmi esterni un'interfaccia di SQL ServerSQL Server per varie attività di manutenzione. to external programs for various maintenance activities. Tali stored procedure estese utilizzano il prefisso xp_.These extended procedures use the xp_ prefix. Per un elenco completo delle stored procedure estese, vedere Stored procedure estese generali (Transact-SQL).For a complete list of extended procedures, see General Extended Stored Procedures (Transact-SQL).

Definite dall'utente esteseExtended User-Defined
Le stored procedure estese permettono di creare routine esterne in un linguaggio di programmazione come C e sono DLL che possono essere caricate ed eseguite in modo dinamico da un'istanza di SQL ServerSQL Server .Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL ServerSQL Server can dynamically load and run.

Nota

Le stored procedure estese verranno eliminate nelle future versioni SQL ServerSQL Server.Extended stored procedures will be removed in a future version of SQL ServerSQL Server. Non usare questa funzionalità in un nuovo progetto di sviluppo e modificare non appena possibile le applicazioni in cui è attualmente implementata.Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. In alternativa, creare stored procedure CLR.Create CLR procedures instead. Questo metodo offre un'alternativa più efficiente rispetto alla scrittura di stored procedure estese.This method provides a more robust and secure alternative to writing extended procedures.

Descrizione dell'attivitàTask Description ArgomentoTopic
Viene descritto il processo di creazione di una stored procedure.Describes how to create a stored procedure. Creare una stored procedureCreate a Stored Procedure
Viene descritto il processo di modifica di una stored procedure.Describes how to modify a stored procedure. Modificare una stored procedureModify a Stored Procedure
Viene descritto il processo di eliminazione di una stored procedure.Describes how to delete a stored procedure. Eliminare una stored procedureDelete a Stored Procedure
Viene descritto come eseguire una stored procedure.Describes how to execute a stored procedure. Eseguire una stored procedureExecute a Stored Procedure
Viene descritto come concedere autorizzazioni per una stored procedure.Describes how to grant permissions on a stored procedure. Concedere autorizzazioni per una stored procedureGrant Permissions on a Stored Procedure
Viene descritto come restituire dati da una stored procedure a un'applicazione.Describes how to return data from a stored procedure to an application. Restituire dati da una stored procedureReturn Data from a Stored Procedure
Viene descritto come ricompilare una stored procedure.Describes how to recompile a stored procedure. Ricompilare una stored procedureRecompile a Stored Procedure
Viene descritto come ridenominare una stored procedure.Describes how to rename a stored procedure. Rinominare una stored procedureRename a Stored Procedure
Viene descritto come visualizzare la definizione di una stored procedure.Describes how to view the definition of a stored procedure. Visualizzare la definizione di una stored procedureView the Definition of a Stored Procedure
Viene descritto come visualizzare ciò che dipende da una stored procedure.Describes how to view the dependencies on a stored procedure. Visualizzare le dipendenze di una stored procedureView the Dependencies of a Stored Procedure
Viene descritta la modalità d'uso dei parametri in una stored procedure.Describes how Parameters are used in a stored procedure. ParametriParameters

Stored procedure CLRCLR Stored Procedures