CREATE PROCEDURE (Transact-SQL)

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW) Endpoint di analisi SQL in Microsoft Fabric Warehouse in Microsoft Fabric

Crea una stored procedure Transact-SQL o CLR (Common Language Runtime) in SQL Server, nel database SQL di Azure e nella Piattaforma di strumenti analitici (PDW). 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 routine permanente nel database corrente o una routine temporanea nel tempdb database.

Nota

In questo argomento viene illustrata l'integrazione di CLR di .NET Framework in SQL Server. L'integrazione di CLR non si applica al database SQL di Azure.

Passare a Semplici esempi per ignorare i dettagli della sintassi e ottenere un rapido esempio di una stored procedure di base.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi Transact-SQL per stored procedure in SQL Server e database SQL di Azure:

syntaxsql

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

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

Sintassi Transact-SQL per stored procedure CLR:

syntaxsql

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Sintassi Transact-SQL per stored procedure compilate in modo nativo:

syntaxsql

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

Sintassi Transact-SQL per stored procedure in Azure Synapse Analytics e Parallel Data Warehouse:

syntaxsql

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Sintassi Transact-SQL per le stored procedure in Microsoft Fabric:

syntaxsql

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Argomenti

OR ALTER

Si applica a: Azure SQL Database, SQL Server (a partire da SQL Server 2016 (13.x) SP1).

Modifica la procedura, se esiste già.

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.

Attenzione

Evitare l'uso del prefisso durante la sp_ denominazione delle procedure. Questo prefisso viene usato 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 stored procedure temporanee locali o globali possono essere create usando un simbolo di cancelletto (#) prima di procedure_name (#procedure_name) per le stored procedure temporanee locali e due simboli di cancelletto per quelle globali (##procedure_name). Una stored procedure temporanea locale è visibile solo alla connessione da cui è stata creata e, alla chiusura di quest'ultima, viene eliminata. Una stored procedure temporanea globale è disponibile per tutte le connessioni e viene eliminata al termine dell'ultima sessione che la usano. Non è possibile specificare nomi temporanei per le procedure CLR.

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

; number

Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.

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

Nota

Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare 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 usate in una guida di piano.

@parameter_name

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

È possibile dichiarare 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 altre 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 usati come parametri.
• Per creare parametri con valori di tabella è possibile usare il tipo di tabella definito dall'utente. I parametri con valori di tabella possono essere solo parametri di input e devono essere associati alla parola chiave READONLY. Per altre informazioni, vedere Usare parametri con valori di tabella (Motore di database)
• I tipi di dati cursor possono essere solo parametri OUTPUT e devono essere associati alla 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 usati come parametri. Per altre informazioni sulla corrispondenza tra tipi CLR e tipi di dati di sistema di SQL Server, vedere Mapping dei dati dei parametri CLR. Per altre informazioni sui tipi di dati di sistema di SQL Server e sulla relativa sintassi, vedere Tipi di dati (Transact-SQL).

• I tipi di dati con valori di tabella o cursor non possono essere usati 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. Si applica solo a parametri 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 usare la parola chiave LIKE quando si passa il parametro nella procedura.

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

OUT | OUTPUT

Indica che si tratta di un parametro di output. Utilizzare i parametri di output per restituire valori al chiamante della procedura. Non è possibile usare i tipi text, ntext e image 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 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, che quindi verrà compilata a ogni esecuzione. Per altre informazioni sui motivi della ricompilazione forzata, vedere Ricompilare una stored procedure. Questa opzione non può essere usata per le 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, usare l'hint per la query RECOMPILE nella definizione della query. Per altre informazioni, vedere Hint per la query (Transact-SQL).

ENCRYPTION

Si applica a: SQL Server ( SQL Server 2008 (10.0.x) e versioni successive), database SQL di Azure.

Indica che SQL Server converte il testo originale dell'istruzione CREATE PROCEDURE in un formato offuscato. In SQL Server l'output in formato offuscato non è visibile direttamente in alcuna vista del catalogo. Il testo offuscato non può essere recuperato da utenti che non hanno accesso a file di database o tabelle di sistema. Tale testo, tuttavia, è 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, agli utenti che possono collegare un debugger al processo del server è consentito recuperare la procedura decrittografata dalla memoria in fase di esecuzione. Per altre 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 clause

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

Per le stored procedure compilate in modo nativo, a partire da SQL Server 2016 (13.x) e nel database SQL di Azure non esistono limitazioni per la clausola EXECUTE AS. In SQL Server 2014 (12.x) le clausole SELF, OWNER e 'user_name' sono supportate con stored procedure compilate in modo nativo.

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

FOR REPLICATION

Si applica a: SQL Server ( SQL Server 2008 (10.0.x) e versioni successive), database SQL di Azure.

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 usata 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 le procedure CLR. L'opzione RECOMPILE viene ignorata per le procedure create con l'opzione FOR REPLICATION.

Una FOR REPLICATION routine ha 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 usare 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

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL.

Specifica il metodo di un assembly .NET Framework affinché una procedura CLR vi faccia riferimento. class_name deve essere un identificatore valido di SQL Server e deve esistere come classe nell'assembly. Se la classe ha un nome completo dello spazio dei nomi che usa un punto (.) per separare le parti dello spazio dei nomi, il nome della classe deve essere delimitato usando parentesi quadre () o virgolette ([]""). Il metodo specificato deve essere un metodo statico della classe.

Per impostazione predefinita, SQL Server non può eseguire il codice CLR. È 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 option. Per abilitare questa opzione, usare sp_configure.

Nota

Le procedure CLR non sono supportate in un database indipendente.

ATOMIC WITH

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Indica l'esecuzione atomica di stored procedure. Viene eseguito il commit delle modifiche o il rollback di tutte le modifiche tramite la generazione di un'eccezione. Il blocco ATOMIC WITH è obbligatorio per le stored procedure compilate in modo nativo.

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. Se la procedura esegue THROW, viene eseguito il rollback del lavoro svolto dalla procedura.

L'opzione XACT_ABORT è ON per impostazione predefinita in un blocco ATOMIC e non può essere modificata. XACT_ABORT specifica se in SQL Server viene eseguito automaticamente il rollback della transazione corrente quando un'istruzione Transact-SQL genera un errore di run-time.

Le opzioni SET seguenti sono sempre impostate su ON nel blocco ATOMIC e non possono essere modificate.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

Le opzioni SET non possono essere modificate nei blocchi ATOMIC. Le opzioni SET nella sessione utente non vengono usate nell'ambito delle stored procedure compilate in modo nativo. Queste opzioni vengono fissate in fase di compilazione.

Le operazioni BEGIN, ROLLBACK e COMMIT non possono essere usate in un blocco ATOMIC.

Esiste un solo blocco ATOMIC per stored procedure compilata in modo nativo, nell'ambito esterno della procedura. I blocchi non possono essere annidati. Per altre informazioni sui blocchi ATOMIC, vedere stored procedure compilate in modo nativo.

NULL | NOT NULL

Determina se i valori Null sono supportati in un parametro. Il valore predefinito è NULL.

NATIVE_COMPILATION

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Indica che la procedura è compilata in modo nativo. NATIVE_COMPILATION, SCHEMABINDING ed EXECUTE AS possono essere specificati in qualsiasi ordine. Per altre informazioni, vedere Natively Compiled Stored Procedures (Stored procedure compilate in modo nativo).

SCHEMABINDING

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Assicura che le tabelle a cui si fa riferimento in una procedura non possano essere eliminate o modificate. SCHEMABINDING è obbligatorio nelle stored procedure compilate in modo nativo. Per altre informazioni, vedere Stored procedure compilate in modo nativo. Le restrizioni SCHEMABINDING sono le stesse delle funzioni definite dall'utente. Per altre informazioni, vedere la sezione SCHEMABINDING in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Equivalente a un'opzione di una sessione SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'lingua' è obbligatorio.

TRANSACTION ISOLATION LEVEL

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Obbligatorio per stored procedure compilate in modo nativo. Specifica il livello di isolamento della transazione della stored procedure. Le opzioni sono le seguenti:

Per altre informazioni su queste opzioni, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READ

Specifica che le istruzioni non possono leggere i dati modificati da altre transazioni ma di cui non è ancora stato eseguito il commit. Se un'altra transazione modifica i dati letti dalla transazione corrente, quest'ultima non riesce.

SERIALIZABLE

Specifica quanto segue:

• Le istruzioni non possono leggere dati modificati da altre transazioni ma di cui non è ancora stato eseguito il commit.
• Se un'altra transazione modifica i dati letti dalla transazione corrente, quest'ultima non riesce.
• Se un'altra transazione inserisce nuove righe con valori di chiave che rientrano nell'intervallo di chiavi lette da qualsiasi istruzione nella transazione corrente, quest'ultima non riesce.

SNAPSHOT

specifica che i dati letti da qualsiasi istruzione in una transazione rappresentano la versione coerente dal punto di vista transazionale dei dati esistenti al momento dell'avvio della transazione.

DATEFIRST = number

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Specifica il primo giorno della settimana come numero compreso tra 1 e 7. DATEFIRST è facoltativo. Se viene omesso, l'impostazione viene dedotta dalla lingua specificata.

Per altre informazioni, vedere SET DATEFIRST (Transact-SQL).

DATEFORMAT = format

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

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. DATEFORMAT è facoltativo. Se viene omesso, l'impostazione viene dedotta dalla lingua specificata.

Per altre informazioni, vedere SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Il commit delle transazioni di SQL Server può essere completamente durevole, ovvero l'impostazione predefinita di SQL Server, oppure con durabilità ritardata.

Per altre informazioni, vedere Controllo della durabilità delle transazioni.

Esempi semplici

Per iniziare, di seguito sono riportati due esempi rapidi: SELECT DB_NAME() AS ThisDB; restituisce il nome del database corrente. È possibile eseguire il wrapping di tale istruzione in una stored procedure, ad esempio:

SQL

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Chiamare la stored procedure con l'istruzione: EXEC What_DB_is_this;

Un'operazione leggermente più complessa consiste nello specificare un parametro di input per rendere la procedura più flessibile. Ad esempio:

SQL

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

Specificare un ID database quando si chiama la procedura. Ad esempio EXEC What_DB_is_that 2; restituisce tempdb.

Per numerosi altri esempi, vedere Esempi verso la fine di questo articolo.

Procedure consigliate

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

• Usare 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. Ciò consente di limitare al minimo le dimensioni dell'output generato per maggiore chiarezza. Non viene tuttavia riscontrato alcun vantaggio misurabile in termini di prestazioni nell'hardware attuale. Per informazioni, vedere SET NOCOUNT (Transact-SQL).
• Usare i nomi degli schemi quando si creano oggetti di database nella procedura o vi si fa riferimento. La risoluzione dei nomi degli oggetti da parte del motore di database richiede un tempo di elaborazione minore se la ricerca non deve essere effettuata in più schemi. È anche possibile evitare problemi di autorizzazione 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'uso 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 delle 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 usando 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 usare set di risultati inutilmente grandi.
• Usare le transazioni esplicite tramite BEGIN/COMMIT TRANSACTION mantenendole più brevi 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 usare 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.
• Usare 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.
• Usare 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.
• 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. 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 usano le funzioni di aggregazione.
• Usare l'operatore UNION ALL invece dell'operatore UNION oppure OR, a meno che non siano necessari valori distinct. L'operatore UNION ALL richiede un minor sovraccarico di elaborazione poiché i duplicati non vengono esclusi dal set di risultati.

Osservazioni:

Non è prevista una dimensione massima predefinita per una procedura.

Le variabili specificate nella procedura possono essere variabili definite dall'utente o 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 nella cache dei piani 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 di sistema nel master database ed eseguite nel ruolo predefinito del server sysadmin come processo in background. In queste procedure non è possibile usare parametri di input o output. Per altre informazioni, vedere Eseguire una stored procedure.

Le procedure vengono annidate 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 annidamento. 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. È possibile usare la funzione @@NESTLEVEL per restituire il livello di annidamento dell'esecuzione della stored procedure corrente.

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 usate 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, la variabile viene definita come char(3) e quindi 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 usata in combinazione con altre istruzioni Transact-SQL all'interno di un singolo batch.

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

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

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. È quindi 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 ha 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.

SQL

-- 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.
• Usare 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 UFX

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

Visualizza	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, usare 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
	Numero oggetti nella cache 1

¹ 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 altre informazioni, vedere Oggetto Plan Cache di SQL Server.

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.

CREATE PROCEDURE e tabelle ottimizzate per la memoria

È possibile accedere alle tabelle ottimizzate per la memoria da stored procedure compilate sia in modo tradizionale che in modo nativo. Nella maggior parte dei casi, le stored procedure native sono più efficienti. Per altre informazioni, vedere Natively Compiled Stored Procedures (Stored procedure compilate in modo nativo).

L'esempio seguente illustra come creare una stored procedure compilata in modo nativo che accede a una tabella ottimizzata per la memoria, dbo.Departments:

SQL

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.

Per informazioni sulla programmabilità nelle stored procedure compilate in modo nativo, sulla superficie di attacco delle query supportata e sugli operatori, vedere Funzionalità supportate per i moduli T-SQL compilati in modo nativo.

Esempi

Categoria	Elementi di sintassi inclusi
Sintassi di base	CREATE PROCEDURE
Passaggio di parametri	@parameter = default PRODOTTO Tipo di parametro con valori di tabella CURSOR VARYING
Modifica dei dati tramite una stored procedure	UPDATE
Gestione degli errori	TRY...CATCH
Offuscamento della definizione della procedura	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.

R. Creare una procedura Transact-SQL

Nell'esempio seguente viene creata una stored procedure che restituisce tutti i dipendenti (nome e cognome specificati), i titoli dei processi e i relativi nomi di reparto da una vista nel database AdventureWorks2022. In questa procedura non viene usato alcun parametro. Nell'esempio vengono quindi illustrati tre metodi di esecuzione della procedura.

SQL

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

SELECT * FROM HumanResources.vEmployeeDepartment;

La procedura uspGetEmployees può essere eseguita nei modi seguenti:

SQL

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

B. Restituire più di un set di risultati

Tramite la procedura seguente vengono restituiti due set di risultati.

SQL

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

C. Creare una stored procedure CLR

L'esempio seguente crea 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.

Si applica a: SQL Server 2008 (10.0.x) e versioni successive, database SQL (se si usa un assembly creato da assembly_bits.

SQL

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

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

D. Creare 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.

SQL

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

La procedura uspGetEmployees può essere eseguita nei modi seguenti:

SQL

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

SQL

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;

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

SQL

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. Usare parametri OUTPUT

Nell'esempio seguente viene creata la procedura uspGetList. Questa procedura 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.

SQL

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 usati 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 usata 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 usata la sintassi @ListPrice = variabile.

SQL

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:

Output

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. Usare un parametro con valori di tabella

Nell'esempio seguente viene usato un tipo di parametro con valori di tabella per inserire più righe in una tabella. 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, usati da quest'ultima per inserire più righe in una tabella.

SQL

/* 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 [AdventureWorks2022].[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
    [AdventureWorks2022].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

H. Usare un parametro OUTPUT di tipo cursore

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.

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

SQL

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.

SQL

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

Modificare i 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.

I. Usare UPDATE in una stored procedure

Nell'esempio seguente viene usata un'istruzione UPDATE in una stored procedure. Per la stored procedure sono previsti un unico parametro di input @NewHours e un unico parametro di output @RowCount. Il valore del parametro @NewHours viene usato nell'istruzione UPDATE per aggiornare la colonna VacationHours della tabella HumanResources.Employee. Il parametro di output @RowCount viene usato per restituire il numero di righe interessate a una variabile locale. Un'espressione CASE viene usata 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 in @NewHours. In caso contrario, VacationHours viene impostato sul valore specificato in @NewHours.

SQL

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

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.

J. Usare TRY...CATCH

Nell'esempio seguente viene illustrato l'uso di un costrutto TRY...CATCH per restituire informazioni sugli errori rilevati durante l'esecuzione di una stored procedure.

SQL

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;
GO
/* 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;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Offuscare la definizione della procedura

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

K. Usare l'opzione WITH ENCRYPTION

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

Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.

SQL

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 nel catalogo di sistema o di uso di funzioni dei metadati, come illustrato negli esempi seguenti.

Eseguire sp_helptext:

SQL

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Il set di risultati è il seguente.

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

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

SQL

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

Il set di risultati è il seguente.

Output

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

Nota

La stored procedure di sistema sp_helptext non è supportata in Azure Synapse Analytics. Usare invece la vista del catalogo dell'oggetto sys.sql_modules.

Forzare la ricompilazione della procedura

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

.L Usare l'opzione WITH RECOMPILE

La clausola WITH RECOMPILE risulta utile quando i parametri forniti alla procedura non sono tipici e quando non si vuole memorizzare nella cache o in memoria un nuovo piano di esecuzione.

SQL

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;

Impostare il contesto di sicurezza

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.

M. Usare la clausola EXECUTE AS

L'esempio seguente illustra l'uso 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.

SQL

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. Creare set di autorizzazioni personalizzati

Nell'esempio seguente viene usata 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.

SQL

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

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

.O Creare una stored procedure che esegue un'istruzione SELECT

Questo esempio illustra la sintassi di base per la creazione e l'esecuzione di una procedura. Quando si esegue un batch, CREATE PROCEDURE deve essere la prima istruzione. Ad esempio, per creare la stored procedure seguente in AdventureWorksPDW2022, impostare prima il contesto del database e quindi eseguire l'istruzione CREATE PROCEDURE.

SQL

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

--Show 10 Top Resellers
EXEC Get10TopResellers;

Vedi anche

• ALTER PROCEDURE (Transact-SQL)
• Control-of-Flow Language (Transact-SQL)
• Cursori
• Tipi di dati (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• EXECUTE AS (Transact-SQL)
• Stored procedure (Motore di database)
• 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)
• Creazione di una stored procedure
• Usare parametri con valori di tabella (motore di database)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)