CREATE PROCEDURE (Transact-SQL)

Consente di creare una stored procedure Transact-SQL o CLR (Common Language Runtime) in SQL Server 2012. Le stored procedure sono simili alle procedure di altri linguaggi di programmazione in quanto sono in grado di:

• Accettare parametri di input e restituire più valori sotto forma di parametri di output alla procedura o al batch che esegue la chiamata.

• Includere istruzioni di programmazione che eseguono le operazioni nel database, tra cui la chiamata di altre procedure.

• 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).

Utilizzare questa istruzione per creare una procedura permanente nel database corrente o una procedura temporanea nel database tempdb.

Convenzioni della sintassi Transact-SQL

Sintassi

--Transact-SQL Stored Procedure Syntax
CREATE { 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 ]

--CLR Stored Procedure Syntax
CREATE { 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 }
[;]

Argomenti

• schema_name
  Nome dello schema a cui appartiene la procedura. Le procedure sono associate a schema. 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.

• procedure_name
  Nome della procedura. I nomi di procedura devono essere conformi alle regole per gli identificatori e devono essere univoci all'interno dello schema.

  Evitare l'utilizzo del prefisso sp_ per la denominazione delle procedure. Questo prefisso viene utilizzato da SQL Server per definire le procedure di sistema. L'utilizzo del prefisso può comportare l'interruzione del codice dell'applicazione, se è presente una procedura di sistema con lo stesso nome.

  Le procedure temporanee locali o globali possono essere create utilizzando un simbolo di cancelletto (#) prima del procedure_name (#procedure_name) per le procedure temporanee locali e due simboli di cancelletto per le procedure temporanee globali (##procedure_name). Una procedura temporanea locale è visibile solo alla connessione da cui è stata creata e, alla chiusura di quest'ultima, viene eliminata. Una procedura temporanea globale è disponibile per tutte le connessioni e viene eliminata al termine dell'ultima sessione tramite la procedura. Non è possibile specificare nomi temporanei per le procedure CLR.

  Il nome completo di una procedura o di una procedura temporanea globale, inclusi i simboli ##, non deve superare i 128 caratteri. Il nome completo di una procedura temporanea locale, incluso il simbolo #, non deve superare i 116 caratteri.

• ; number
  Integer facoltativo utilizzato per raggruppare le procedure con lo stesso nome. Tali procedure possono essere eliminate contemporaneamente tramite un'istruzione DROP PROCEDURE.

  [!NOTA]

  Questa funzionalità verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.

  Le procedure numerate non possono includere i tipi xml o CLR definiti dall'utente né possono essere utilizzate in una guida di piano.

• @ parameter
  Parametro dichiarato nella procedura. Specificare un nome di parametro utilizzando la chiocciola (@) come primo carattere. Il nome del parametro deve essere conforme alle regole per gli identificatori. Poiché i parametri sono locali rispetto alla procedura, è possibile utilizzare gli stessi nomi di parametro in altre procedure.

  È possibile specificare uno o più parametri con un limite massimo di 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. Se una procedura contiene parametri con valori di tabella e nella chiamata il parametro non è presente, viene passata una tabella vuota. I parametri possono rappresentare solo espressioni costanti, non nomi di tabella, nomi di colonna o nomi di altri oggetti di database. Per ulteriori informazioni, vedere EXECUTE (Transact-SQL).

  Se viene specificata l'opzione FOR REPLICATION, non è possibile dichiarare alcun parametro.

• [ type_schema_name**.** ] data_type
  Tipo di dati del parametro e schema a cui appartiene il tipo di dati.

  Linee guida per le procedure Transact-SQL:

  • Tutti i tipi di dati Transact-SQL possono essere utilizzati come parametri.

  • Per creare parametri con valori di tabella è possibile utilizzare il tipo di tabella definito dall'utente. I parametri con valori di tabella possono essere solo parametri di input e insieme a essi deve essere associata la parola chiave READONLY. Per ulteriori informazioni, vedere Utilizzare parametri con valori di tabella (Motore di database)

  • I tipi di parametri cursor possono essere solo parametri di output e insieme a essi deve essere associata la parola chiave VARYING.

  Linee guida per le procedure CLR:

  • Tutti i tipi di dati nativi di SQL Server per cui è presente un equivalente nel codice gestito possono essere utilizzati come parametri. Per ulteriori informazioni sulla corrispondenza tra tipi CLR e tipi di dati di sistema di SQL Server, vedere Mapping dei dati dei parametri CLR. Per ulteriori informazioni sui tipi di dati di sistema di SQL Server e la relativa sintassi, vedere Tipi di dati (Transact-SQL).

  • I tipi di dati con valori di tabella o cursor non possono essere utilizzati come parametri.

  • Se al parametro è stato assegnato un tipo di dati CLR definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per il tipo.

• VARYING
  Specifica il set di risultati supportato come parametro di output. Questo parametro viene creato in modo dinamico dalla procedura e il relativo contenuto può variare. Viene utilizzato solo con parametri di tipo cursor. Questa opzione non è valida per le procedure CLR.

• default
  Valore predefinito per un parametro. Se per un parametro viene definito un valore predefinito, la procedura può essere eseguita senza specificare un valore per tale parametro. Il valore predefinito deve essere una costante oppure NULL. Il formato del valore della costante può essere un carattere jolly; in questo modo sarà possibile utilizzare la parola chiave LIKE quando si passa il parametro nella procedura. Vedere l'esempio C riportato di seguito.

  I valori predefiniti vengono registrati nella colonna sys.parameters.default solo per procedure CLR. La colonna sarà NULL per i parametri di procedure Transact-SQL.

• OUT | OUTPUT
  Indica che si tratta di un parametro di output. Utilizzare i parametri OUTPUT per restituire i valori al chiamante della procedura. I parametri text, ntext e image non posso essere utilizzati come parametri OUTPUT, a meno che non si tratti di una procedura CLR. Un parametro di output può essere un segnaposto del cursore, a meno che non si tratti di una procedura CLR. Un tipo di dati con valori di tabella non può essere specificato come parametro di output di una procedura.

• READONLY
  Indica che il parametro non può essere aggiornato o modificato all'interno del corpo della procedura. Se si tratta di un tipo di parametro con valori di tabella, è necessario specificare la parola chiave READONLY.

• RECOMPILE
  Indica che il Motore di database non consente di memorizzare nella cache un piano di query per questa procedura, pertanto verrà compilato a ogni esecuzione. Per ulteriori informazioni sui motivi della ricompilazione forzata, vedere Ricompilare una stored procedure. Questa opzione non può essere utilizzata per procedure CLR o se si specifica FOR REPLICATION.

  Per indicare al Motore di database di ignorare i piani di singole query all'interno di una procedura, utilizzare l'hint per la query RECOMPILE nella definizione della query. Per ulteriori informazioni, vedere Hint per la query (Transact-SQL).

• ENCRYPTION
  Indica che in SQL Server il testo originale dell'istruzione CREATE PROCEDURE verrà convertito in un formato offuscato. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo in SQL Server. Il testo offuscato non può essere recuperato da utenti che non hanno accesso a tabelle di sistema o da file del database. Tuttavia, tale testo sarà disponibile per gli utenti con privilegi di accesso a tabelle di sistema attraverso la porta DAC o con privilegi di accesso diretto a file del database. Inoltre, gli utenti che possono collegare un debugger al processo del server possono recuperare la procedura decrittografata dalla memoria in fase di esecuzione. Per ulteriori informazioni sull'accesso ai metadati di sistema, vedere Configurazione della visibilità dei metadati

  Questa opzione non è valida per le procedure CLR.

  Le procedure create con questa opzione non possono essere pubblicate durante la replica di SQL Server.

• EXECUTE AS
  Specifica il contesto di sicurezza in cui deve essere eseguita la procedura.

  Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).

• FOR REPLICATION
  Specifica che la procedura viene creata per la replica. Di conseguenza, non può essere eseguita nel Sottoscrittore. Una procedura creata con l'opzione FOR REPLICATION viene utilizzata come filtro di procedura ed eseguita solo durante la replica. Se viene specificata l'opzione FOR REPLICATION, non è possibile dichiarare alcun parametro. Inoltre, l'opzione FOR REPLICATION non può essere specificata per procedure CLR. L'opzione RECOMPILE viene ignorata per le procedure create con l'opzione FOR REPLICATION.

  Una procedura FOR REPLICATION include un tipo di oggetto RF in sys.objects e sys.procedures.

• { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
  Una o più istruzioni Transact-SQL che includono il corpo della procedura. Per racchiudere le istruzioni è possibile utilizzare le parole chiave facoltative BEGIN ed END. Per informazioni, vedere le sezioni Procedure consigliate, Osservazioni generali e Limitazioni e restrizioni riportate di seguito.

• EXTERNAL NAME assembly_name**.class_name.method_name
  Specifica il metodo di un assembly .NET Framework affinché una procedura CLR vi faccia riferimento. class_name deve essere un identificatore SQL Server valido e deve esistere come una classe nell'assembly. Se alla classe è stato assegnato un nome completo con lo spazio dei nomi le cui parti sono separate da un punto (.), il nome della classe deve essere delimitato tramite parentesi ([** ]) o virgolette (" "). Il metodo specificato deve essere un metodo statico della classe.

  Per impostazione predefinita, il codice CLR non può essere eseguito in SQL Server. È possibile creare, modificare ed eliminare gli oggetti di database che fanno riferimento a moduli CLR; tuttavia non è possibile eseguire questi riferimenti in SQL Server finché non viene abilitata l'opzione clr enabled. Per abilitare questa opzione, utilizzare sp_configure.

  [!NOTA]

  Le procedure CLR non sono supportate in un database indipendente.

Procedure consigliate

Sebbene non siano elencate tutte le procedure consigliate, questi suggerimenti possono migliorare le prestazioni della procedura.

• Utilizzare l'istruzione SET NOCOUNT ON come prima istruzione nel corpo della procedura, ovvero posizionarla subito dopo la parola chiave AS. In questo modo vengono disabilitati i messaggi restituiti al client da SQL Server dopo l'esecuzione delle istruzioni SELECT, INSERT, UPDATE, MERGE e DELETE. Le prestazioni generali del database e dell'applicazione vengono migliorate eliminando questo overhead di rete. Per ulteriori informazioni, vedere SET NOCOUNT (Transact-SQL).

• Utilizzare i nomi degli schemi quando si crea o si fa riferimento agli oggetti di database nella procedura. La risoluzione dei nomi degli oggetti da parte del Motore di database richiederà un minor tempo di elaborazione se la ricerca non deve essere effettuata in più schemi. Inoltre sarà possibile evitare problemi di autorizzazioni e accesso causati dall'assegnazione dello schema predefinito di un utente quando gli oggetti vengono creati senza specificare lo schema.

• Evitare l'esecuzione del wrapping di funzioni attorno alle colonne specificate nelle clausole WHERE e JOIN. In tal modo le colonne vengono rese non deterministiche e si evita l'utilizzo di indici in Query Processor.

• Evitare l'utilizzo di funzioni scalari nelle istruzioni SELECT che restituiscono molte righe di dati. 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.

• Evitare l'utilizzo di SELECT *. Specificare invece i nomi delle colonne necessarie. In questo modo è possibile evitare alcuni errori del Motore di database che causano l'arresto dell'esecuzione della procedura. Ad esempio, un'istruzione SELECT * che restituisce i dati di una tabella costituita da 12 colonne e, successivamente, inserisce tali dati in una tabella temporanea di 12 colonne viene eseguita correttamente finché non viene modificato il numero o l'ordine di colonne in una delle tabelle.

• Evitare l'elaborazione o la restituzione di troppi dati. Non appena possibile, restringere i risultati nel codice della procedura in modo che le operazioni successive effettuate dalla procedura vengano eseguite utilizzando il set di dati più piccolo possibile. Inviare solo i dati essenziali all'applicazione client. L'operazione è più efficace dell'invio di dati aggiuntivi nella rete, nonché dell'imposizione all'applicazione client di utilizzare set di risultati inutilmente grandi.

• Utilizzare le transazioni esplicite tramite BEGIN/END TRANSACTION mantenendole più corte possibili. Transazioni lunghe implicano un blocco dei record più lungo e un rischio maggiore di deadlock.

• Per la gestione degli errori all'interno di una procedura utilizzare la funzionalità TRY…CATCH di Transact-SQL che consente di incapsulare un blocco intero di istruzioni Transact-SQL. In questo modo vengono garantiti un minor overhead delle prestazioni e una segnalazione errori più precisa con un utilizzo inferiore della programmazione.

• Utilizzare la parola chiave DEFAULT in tutte le colonne della tabella a cui viene fatto riferimento dalle istruzioni Transact-SQL CREATE TABLE o ALTER TABLE presenti nel corpo della procedura. In questo modo è possibile evitare di passare NULL alle colonne che non accettano valori Null.

• Utilizzare NULL o NOT NULL per ogni colonna di una tabella temporanea. 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 database quando tale attributo non è specificato in un'istruzione CREATE TABLE o ALTER TABLE. 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. 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.

• Utilizzare 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. Tenere presente che in Transact-SQL NULL non è un valore vuoto o "Nothing". 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 utilizzano le funzioni di aggregazione.

• Utilizzare l'operatore UNION ALL invece degli operatori UNION oppure OR, a meno che non siano necessari valori distinct. L'operatore UNION ALL richiede un minor overhead di elaborazione poiché i duplicati non vengono esclusi dal set di risultati.

Osservazioni generali

Non è prevista una dimensione massima predefinita per una procedura.

Le variabili specificate nella procedura possono essere definite dall'utente o possono essere variabili di sistema, ad esempio @@SPID.

Alla prima esecuzione, la procedura viene compilata in modo da determinare un piano di accesso ottimale per il recupero dei dati. Se il piano generato rimane archiviato nell'apposita cache del Motore di database, può essere riutilizzato nelle successive esecuzioni della procedura.

È possibile eseguire automaticamente una o più procedure all'avvio di SQL Server. Le procedure devono essere create dall'amministratore del sistema nel database master ed eseguite dal ruolo predefinito del server sysadmin come processo in background. In queste procedure non è possibile utilizzare parametri di input o output. Per ulteriori informazioni, vedere Eseguire una 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. È possibile nidificare fino a 32 livelli di procedure e riferimenti a codice gestito. 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. I metodi richiamati all'interno del codice gestito non vengono inclusi nel limite del livello di nidificazione. 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.

Il tentativo di superare il livello di nidificazione massimo causa l'esito negativo dell'intera catena di chiamata. Per restituire il livello di nidificazione dell'esecuzione della stored procedure corrente è possibile utilizzare la funzione @@NESTLEVEL.

Interoperabilità

Quando viene creata o modificata una procedura Transact-SQL, nel Motore di database vengono salvate le impostazioni di entrambe le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS. Queste impostazioni originali vengono utilizzate quando viene eseguita la procedura. Pertanto, le impostazioni di sessione del client per le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS vengono ignorate durante l'esecuzione della procedura.

Altre opzioni SET, ad esempio SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, non vengono salvate quando viene creata o modificata una procedura. 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. Quando un'istruzione SET viene eseguita da una procedura, l'impostazione rimane attiva solo fino al termine dell'esecuzione della procedura. L'impostazione viene quindi ripristinata al valore assegnato alla procedura quando è stata chiamata. In tal modo nei singoli client è possibile impostare le opzioni desiderate senza influire sulla logica della procedura.

In una procedura è possibile specificare qualsiasi istruzione SET, ad eccezione di SET SHOWPLAN_TEXT e SET SHOWPLAN_ALL. Queste devono essere le uniche istruzioni in un batch. L'opzione SET scelta rimane attiva durante l'esecuzione della procedura, dopodiché viene ripristinata l'impostazione precedente.

[!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. Se, ad esempio, una variabile viene definita come char(3) e, successivamente, impostata su un valore maggiore di tre caratteri, i dati verranno troncati alla dimensione definita e l'istruzione INSERT o UPDATE avrà esito positivo.

Limitazioni e restrizioni

L'istruzione CREATE PROCEDURE non può essere utilizzata in combinazione con altre istruzioni Transact-SQL all'interno di un singolo batch.

Le istruzioni seguenti non possono essere utilizzate in un qualsiasi punto del corpo di una stored procedure.

CREATE AGGREGATE	CREATE SCHEMA	SET SHOWPLAN_TEXT
CREATE DEFAULT	CREATE o ALTER TRIGGER	SET SHOWPLAN_XML
CREATE o ALTER FUNCTION	CREATE o ALTER VIEW	USE database_name
CREATE o ALTER PROCEDURE	SET PARSEONLY
CREATE RULE	SET SHOWPLAN_ALL

Una procedura può fare riferimento a tabelle che non esistono ancora. In fase di creazione viene eseguito solo un controllo della sintassi. La procedura non viene compilata fino alla prima esecuzione ed è solo durante la compilazione che vengono risolti tutti gli oggetti a cui viene fatto riferimento nella procedura. Pertanto, è possibile creare una procedura con sintassi corretta che fa riferimento a tabelle non ancora esistenti. Se, tuttavia, le tabelle a cui viene fatto riferimento non esistono in fase di esecuzione, la procedura avrà esito negativo.

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. Tuttavia, è possibile passare una funzione come variabile, come illustrato nell'esempio seguente.

-- 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 Server, non è possibile eseguire il rollback delle modifiche. Le procedure remote non partecipano alle transazioni.

Affinché il Motore di database 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:

• Essere dichiarato come metodo statico.

• Ricevere lo stesso numero di parametri della procedura.

• Utilizzare tipi di parametro compatibili con i tipi di dati dei parametri corrispondenti della procedura di SQL Server. Per informazioni sulla corrispondenza tra i tipi di dati di SQL Server e i tipi di dati di .NET Framework, vedere Mapping dei dati dei parametri CLR.

Metadati

Nella tabella seguente sono elencate le viste del catalogo e le DMV utilizzabili per restituire informazioni sulle stored procedure.

Vista	Descrizione
sys.sql_modules	Viene restituita la definizione di una procedura Transact-SQL. Il testo di una procedura creata con l'opzione ENCRYPTION non può essere visualizzato tramite la vista del catalogo sys.sql_modules.
sys.assembly_modules	Vengono restituite informazioni su una procedura CLR.
sys.parameters	Vengono restituite informazioni sui parametri definiti in una procedura.
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Vengono restituiti gli oggetti a cui una procedura fa riferimento.

Per stimare le dimensioni di una procedura compilata, utilizzare i seguenti contatori di Performance Monitor.

Nome dell'oggetto di Performance Monitor	Nome del contatore di Performance Monitor
SQLServer: Plan Cache Object	Percentuale riscontri cache
	Pagine cache
	Conteggio oggetti cache*

*Questi contatori sono disponibili per diverse categorie di oggetti della cache, inclusi istruzioni Transact-SQL ad hoc e preparate, procedure, trigger e così via. Per ulteriori informazioni, vedere Oggetto Plan Cache di SQL Server.

Sicurezza

Autorizzazioni

Sono richieste l'autorizzazione CREATE PROCEDURE per il database e l'autorizzazione ALTER per lo schema in cui viene creata la procedura oppure è richiesta l'appartenenza al ruolo predefinito del database db_ddladmin.

Per le stored procedure CLR è necessario essere proprietari dell'assembly a cui viene fatto riferimento nella clausola EXTERNAL NAME oppure disporre dell'autorizzazione REFERENCES per questo assembly.

Esempi

Categoria	Elementi di sintassi inclusi
Sintassi di base	CREATE PROCEDURE
Passaggio di parametri	@parameter • = default • OUTPUT • table-valued parameter type • CURSOR VARYING
Modifica dei dati tramite una stored procedure	UPDATE
Gestione degli errori	TRY…CATCH
Offuscamento della definizione delle procedure	WITH ENCRYPTION
Ricompilazione forzata della procedura	WITH RECOMPILE
Impostazione del contesto di sicurezza	EXECUTE AS

Sintassi di base

Negli esempi contenuti in questa sezione vengono illustrate le funzionalità di base dell'istruzione CREATE PROCEDURE tramite la sintassi minima necessaria.

A.Creazione di una procedura Transact-SQL semplice

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. In questa procedura non viene utilizzato alcun parametro. Nell'esempio vengono quindi illustrati tre metodi di esecuzione della procedura.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

La procedura uspGetEmployees può essere eseguita nei modi seguenti:

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

B.Restituzione di più di un set di risultati

Tramite la procedura seguente vengono restituiti due set di risultati.

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

C.Creazione di una stored procedure CLR

Nell'esempio seguente viene creata la procedura GetPhotoFromDB che fa riferimento al metodo GetPhotoFromDB della classe LargeObjectBinary nell'assembly HandlingLOBUsingCLR . Prima della creazione della procedura, l'assembly HandlingLOBUsingCLR viene registrato nel database locale.

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 parametri

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.

A.Creazione di una procedura con parametri di input

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. In questa procedura vengono accettate solo corrispondenze esatte per i parametri passati.

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

La procedura uspGetEmployees può essere eseguita nei modi seguenti:

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

B.Utilizzo di una procedura con parametri di caratteri jolly

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. Lo schema di questa procedura corrisponde ai parametri passati oppure, se non è stato specificato alcun parametro, ai parametri predefiniti (cognomi che iniziano con la lettera D).

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

La procedura uspGetEmployees2 può essere eseguita in molte combinazioni diverse. Di seguito sono riportate solo alcune delle combinazioni possibili.

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

C.Utilizzo di parametri OUTPUT

Nell'esempio seguente viene creata la procedura uspGetList che restituisce un elenco di prodotti il cui prezzo non supera un determinato importo. In questo esempio viene illustrato l'utilizzo di più istruzioni SELECT e di più parametri OUTPUT. I parametri OUTPUT consentono a una procedura esterna, un batch o più istruzioni Transact-SQL di accedere a un valore impostato durante l'esecuzione della procedura.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Eseguire uspGetList per restituire un elenco dei prodotti di Adventure Works (biciclette) con un prezzo inferiore a $700. I parametri OUTPUT @Cost e @ComparePrices vengono utilizzati con elementi del linguaggio per il controllo di flusso per restituire un messaggio nella finestra Messaggi.

[!NOTA]

La variabile OUTPUT deve essere definita sia quando viene creata la procedura che quando viene utilizzata la variabile. Non è necessario che il nome del parametro e il nome della variabile corrispondano. Il tipo di dati e la posizione del parametro devono tuttavia corrispondere, a meno che non venga utilizzata la sintassi @ListPrice = variable.

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:

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.

D.Utilizzo di un parametro con valori di tabella

Nell'esempio seguente viene utilizzato un tipo di parametro con valori di tabella per inserire più righe in una tabella. Nell'esempio viene creato il tipo di parametro, dichiarata una variabile di tabella per farvi riferimento, riempito l'elenco di parametri e, successivamente, vengono passati i valori a una stored procedure, utilizzati da quest'ultima per inserire più righe in una tabella.

USE AdventureWorks2012;
GO

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

E.Utilizzo di un parametro OUTPUT di tipo cursore

Nell'esempio seguente viene utilizzato il parametro OUTPUT di tipo cursore per passare nuovamente al batch, alla procedura o al trigger chiamante un cursore locale rispetto a una procedura.

Creare innanzitutto la procedura che consente di dichiarare e, successivamente, di aprire un cursore nella tabella Currency:

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
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.

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

A.Utilizzo di UPDATE in una stored procedure

Nell'esempio seguente viene utilizzata un'istruzione UPDATE in una stored procedure. Per la stored procedure è previsto un unico parametro di input @NewHours e un unico parametro di output @RowCount. Il valore del parametro @NewHours viene utilizzato nell'istruzione UPDATE per aggiornare la colonna VacationHours della tabella HumanResources.Employee. Il parametro di output @RowCount viene utilizzato per restituire il numero di righe interessate a una variabile locale. Un'espressione CASE viene utilizzata nella clausola SET per determinare in modo condizionale il valore impostato per VacationHours. Quando un dipendente percepisce una paga oraria (SalariedFlag = 0), VacationHours viene impostato sul numero corrente di ore più il valore specificato nella stored procedure @NewHours. In caso contrario, VacationHours viene impostato sul valore specificato in @NewHours.

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

Negli esempi contenuti in questa sezione vengono illustrati i metodi per gestire gli errori che potrebbero verificarsi durante l'esecuzione della stored procedure.

Utilizzo di 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.

USE AdventureWorks2012;
GO

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

Negli esempi contenuti in questa sezione viene illustrato come offuscare la definizione della stored procedure.

A.Utilizzo dell'opzione WITH ENCRYPTION

Nell'esempio seguente viene creata la procedura HumanResources.uspEncryptThis.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

L'opzione WITH ENCRYPTION consente di offuscare la definizione della procedura in caso di query sul catalogo di sistema o di utilizzo di funzioni dei metadati, come illustrato negli esempi seguenti.

Eseguire sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Set di risultati:

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

Eseguire una query diretta sulla vista del catalogo sys.sql_modules:

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

Set di risultati:

definition

--------------------------------

NULL

Ricompilazione forzata della procedura

Negli esempi contenuti in questa sezione viene utilizzata la clausola WITH RECOMPILE per forzare la ricompilazione della procedura a ogni esecuzione.

A.Utilizzo dell'opzione WITH RECOMPILE

La clausola WITH RECOMPILE risulta utile quando la procedura non include parametri tipici e quando non si desidera memorizzare nella cache o archiviare nella memoria un nuovo piano di esecuzione.

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

Impostazione del contesto di sicurezza

Negli esempi contenuti in questa sezione viene utilizzata la clausola EXECUTE AS per impostare il contesto di sicurezza in cui viene eseguita la stored procedure.

A.Utilizzo della clausola EXECUTE AS

Nell'esempio seguente viene illustrato l'utilizzo della clausola EXECUTE AS per specificare il contesto di sicurezza in cui può essere eseguita una procedura. In questo esempio l'opzione CALLER consente di specificare che la procedura può essere eseguita nel contesto dell'utente che la chiama.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
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

B.Creazione di set di autorizzazioni personalizzate

Nell'esempio seguente viene utilizzata la clausola EXECUTE AS per creare autorizzazioni personalizzate per un'operazione sul database. Per alcune operazioni, ad esempio TRUNCATE TABLE, non è possibile concedere le autorizzazioni. 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.

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

Vedere anche

Riferimento

ALTER PROCEDURE (Transact-SQL)

Elementi del linguaggio per il controllo di flusso (Transact-SQL)

Tipi di dati (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

Concetti

Cursori

Stored procedure (Motore di database)

Creazione di una stored procedure

Utilizzare parametri con valori di tabella (Motore di database)