CREATE PROCEDURE (Transact-SQL)
Crea una stored procedure. Una stored procedure è una raccolta salvata di istruzioni Transact-SQL oppure un riferimento a un metodo Common Language Runtime (CLR) di Microsoft .NET Framework in grado di accettare e restituire parametri specificati dall'utente. È possibile creare procedure per un utilizzo permanente, per un utilizzo temporaneo all'interno di una sessione (procedura temporanea locale) oppure per un utilizzo temporaneo all'interno di tutte le sessioni (procedura temporanea globale).
È inoltre possibile creare stored procedure da eseguire automaticamente all'avvio di un'istanza di SQL Server.
.gif "Icona di collegamento a un argomento")
Sintassi
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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
Argomenti
schema_name
Nome dello schema a cui appartiene la procedura.procedure_name
Nome della nuova stored procedure. I nomi di procedura devono essere conformi alle regole per gli identificatori e devono essere univoci all'interno dello schema.È consigliabile evitare di utilizzare il prefisso sp_ nel nome della procedura. Questo prefisso viene utilizzato da SQL Server per contrassegnare stored procedure di sistema. Per ulteriori informazioni, vedere Creazione di stored procedure (Motore di database).
Per creare procedure temporanee locali o globali, utilizzare un simbolo di cancelletto (#) prima di procedure_name (#procedure_name) per le procedure temporanee locali e due simboli di cancelletto per le procedure temporanee globali (##procedure_name). Non è possibile specificare nomi temporanei per stored procedure CLR.
Il nome completo di una stored procedure 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
Valore integer facoltativo utilizzato per raggruppare procedure con lo stesso nome. Queste procedure raggruppate possono essere eliminate contemporaneamente tramite un'istruzione DROP PROCEDURE. Se ad esempio per un'applicazione denominata orders vengono utilizzate procedure denominate orderproc;1, orderproc;2 e così via, l'istruzione DROP PROCEDURE orderproc elimina l'intero gruppo. Se il nome include identificatori delimitati, il numero non deve essere specificato come parte dell'identificatore. Utilizzare il delimitatore adeguato solo per l'argomento procedure_name.Le stored procedure numerate sono soggette alle restrizioni seguenti:
Non è possibile utilizzare tipi di dati CLR definiti dall'utente o xml.
Non è possibile creare una guida di piano.
[!NOTA]
Questa caratteristica 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.
**@**parameter
Parametro della procedura. In un'istruzione CREATE PROCEDURE è possibile dichiarare uno o più parametri. Il valore di ogni parametro dichiarato deve essere specificato dall'utente quando viene chiamata la procedura, a meno che non venga definito un valore predefinito per il parametro oppure il valore venga impostato in modo da corrispondere a quello di un altro parametro. Una stored procedure può includere al massimo 2.100 parametri. Se una procedura contiene parametri con valori di tabella e nella chiamata il parametro non è presente, viene passata una tabella vuota per impostazione predefinita.Per specificare un nome di parametro, utilizzare come primo carattere la chiocciola (@). I nomi di parametro devono essere conformi alle regole per gli identificatori. I parametri sono locali rispetto alla procedura. È pertanto possibile utilizzare gli stessi nomi di parametro in altre procedure. Per impostazione predefinita, i parametri possono rappresentare solo espressioni costanti, non nomi di tabella, nomi di colonna o nomi di altri oggetti del 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. Per una stored procedure Transact-SQL è possibile utilizzare come parametro tutti i tipi di dati. È possibile utilizzare un tipo di tabella definito dall'utente per dichiarare un parametro valutato a livello di tabella come parametro per una stored procedure Transact-SQL. I parametri valutati a livello di tabella possono essere specificati solo come parametri di input e insieme a essi deve essere presente una parola chiave READONLY. Il tipo di dati cursor è supportato solo in parametri OUTPUT. Quando si imposta un tipo di dati cursor, è necessario specificare anche le parole chiave VARYING e OUTPUT. È possibile specificare più parametri di output con il tipo di dati cursor.Per le stored procedure CLR non è possibile specificare come parametri i tipi di dati char, varchar, text, ntext, image, cursor, i tipi di tabella definiti dall'utente e table. 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).
Se al parametro è stato assegnato un tipo di dati CLR definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per il tipo.
Se il parametro type_schema_name viene omesso, Motore di database di SQL Server fa riferimento a type_name nell'ordine seguente:
Tipi di dati di sistema di SQL Server.
Schema predefinito dell'utente corrente nel database corrente.
Schema dbo nel database corrente.
Per le stored procedure numerate non è possibile utilizzare il tipo di dati xml né un tipo CLR definito dall'utente.
VARYING
Specifica il set di risultati supportato come parametro di output. Questo parametro viene creato in modo dinamico dalla stored procedure e il relativo contenuto può variare. Viene utilizzato solo con parametri di tipo cursor.default
Valore predefinito del parametro. Se viene definito un valore default, è possibile eseguire la procedura senza specificare un valore per il parametro. Il valore predefinito deve essere una costante o la parola chiave NULL. Se nella procedura il parametro viene utilizzato con la parola chiave LIKE, è possibile includere i caratteri jolly seguenti: % _ [] e [^].[!NOTA]
I valori predefiniti vengono registrati nella colonna sys.parameters.default solo per procedure CLR. Per i parametri di procedure Transact-SQL questa colonna contiene il valore NULL.
OUTPUT
Specifica che si tratta di un parametro di output. Il valore di questa opzione può essere restituito all'istruzione EXECUTE chiamante. Utilizzare parametri OUTPUT per restituire valori al chiamante della procedura. Non è possibile utilizzare text, ntext e image come tipo di dati per parametri OUTPUT, a meno che non si tratti di una procedura CLR. Un parametro di output in cui viene utilizzata la parola chiave OUTPUT può essere un segnaposto del cursore, a meno che non si tratti di una procedura CLR. Non è possibile specificare un tipo di tabella definito dall'utente come parametro OUTPUT di una stored procedure.READONLY
Indica che il parametro non può essere aggiornato o modificato all'interno del corpo della procedura. Se il tipo del parametro corrisponde a un tipo di tabella definito dall'utente, è necessario specificare la parola chiave READONLY.RECOMPILE
Specifica che Motore di database non memorizza nella cache un piano per la procedura e che la procedura viene compilata in fase di esecuzione. Questa opzione non può essere utilizzata quando viene specificata l'opzione FOR REPLICATION. Inoltre, l'opzione RECOMPILE non può essere specificata per stored procedure CLR.Per indicare a Motore di database di ignorare i piani per singole query all'interno di una stored procedure, utilizzare l'hint per la query RECOMPILE. Per ulteriori informazioni, vedere Hint per la query (Transact-SQL). Utilizzare l'hint per la query RECOMPILE quando esistono valori atipici o temporanei solo in un subset di query appartenenti alla stored procedure.
ENCRYPTION
Specifica che SQL Server convertirà il testo originale dell'istruzione CREATE PROCEDURE in un formato offuscato. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo in SQL Server. Gli utenti che non hanno accesso a tabelle di sistema o file del database non possono recuperare il testo offuscato. Il testo, tuttavia, sarà disponibile per gli utenti con privilegi di accesso a tabelle di sistema attraverso la porta DAC oppure 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 stored procedure CLR.
Le procedure create con questa opzione non possono essere pubblicate durante la replica di SQL Server.
EXECUTE AS
Specifica il contesto di protezione in cui deve essere eseguita la stored procedure.Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).
FOR REPLICATION
Specifica che le stored procedure create per la replica non possono essere eseguite nel Sottoscrittore. Una stored procedure creata con l'opzione FOR REPLICATION viene utilizzata come filtro di stored procedure 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 stored 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.
<sql_statement>
Una o più istruzioni Transact-SQL da includere nella procedura. Per informazioni sulle limitazioni applicabili, vedere la sezione Osservazioni.EXTERNAL NAME assembly_name**.class_name.method_name
Specifica il metodo di un assembly .NET Framework a cui deve fare riferimento una stored procedure CLR. class_name deve essere un identificatore di SQL Server valido e deve esistere come 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.[!NOTA]
Per impostazione predefinita, SQL Server non può eseguire il codice CLR. È possibile creare, modificare ed eliminare oggetti di database che fanno riferimento a moduli CLR, ma non è possibile eseguire questi riferimenti in SQL Server fino a quando non si attiva l'opzione clr enabled. Per attivare questa opzione, utilizzare sp_configure.
Osservazioni
Non è prevista una dimensione massima predefinita per una stored procedure.
È possibile creare una stored procedure definita dall'utente solo nel database corrente. Fanno eccezione le procedure temporanee, che vengono sempre create in tempdb. Se non viene specificato il nome di uno schema, viene utilizzato lo schema predefinito dell'utente che crea la procedura. Per ulteriori informazioni sugli schemi, vedere Separazione tra schema e utente.
L'istruzione CREATE PROCEDURE non può essere utilizzata in combinazione con altre istruzioni Transact-SQL all'interno di un singolo batch.
Per impostazione predefinita, i parametri ammettono valori Null. Se si passa un parametro NULL e tale parametro viene utilizzato in un'istruzione CREATE TABLE o ALTER TABLE contenente un riferimento a una colonna che non ammette valori Null, in Motore di database viene generato un errore. Per evitare che venga passato un valore NULL a una colonna che non supporta questo tipo di valore, aggiungere logica di programmazione alla procedura oppure specificare un valore predefinito per la colonna tramite la parola chiave DEFAULT dell'istruzione CREATE TABLE o ALTER TABLE.
È consigliabile specificare esplicitamente NULL o NOT NULL per ogni colonna di una tabella temporanea. Le opzioni ANSI_DFLT_ON e ANSI_DFLT_OFF determinano la modalità di assegnazione dell'attributo NULL o NOT NULL alle colonne da parte di Motore di database quando tale attributo non è specificato in un'istruzione CREATE TABLE o ALTER TABLE. Se in una connessione viene eseguita una stored procedure 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 viene dichiarato in modo esplicito l'attributo NULL o NOT NULL per ogni colonna, le tabelle temporanee vengono create con la stessa impostazione del supporto di valori Null per tutte le connessioni in cui viene eseguita la stored procedure.
Utilizzo delle opzioni SET
Quando viene creata o modificata una stored procedure Transact-SQL, in 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 stored procedure. Pertanto, le impostazioni di sessione del client per le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS vengono ignorate durante l'esecuzione della stored procedure. Altre opzioni SET, ad esempio SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, non vengono salvate quando viene creata o modificata una stored procedure. Se la logica della stored procedure richiede 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 stored procedure, l'impostazione rimane attiva solo fino al termine dell'esecuzione della stored procedure. Viene quindi ripristinato il valore assegnato all'impostazione quando è stata chiamata la stored procedure. In tal modo nei singoli client è possibile impostare le opzioni desiderate senza influire sulla logica della stored procedure.
[!NOTA]
L'opzione ANSI_WARNINGS non viene applicata quando vengono passati parametri in una stored procedure o una funzione definita dall'utente oppure quando vengono dichiarate e impostate 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 ha esito positivo.
Utilizzo di parametri con stored procedure CLR
Per i parametri di una stored procedure CLR è possibile utilizzare uno qualsiasi dei tipi di dati di sistema scalari di SQL Server.
Affinché Motore di database faccia riferimento al metodo corretto quando viene eseguito l'overload in .NET Framework, il metodo indicato in <method_specifier> deve soddisfare i requisiti seguenti:
Essere dichiarato come metodo statico.
Ricevere lo stesso numero di parametri della procedura.
Non essere un costruttore né un distruttore della relativa classe.
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.
Restituire un valore void oppure un valore di tipo SQLInt32, SQLInt16, System.Int32 o System.Int16.
Restituire i relativi parametri per riferimento e non per valore quando viene specificata la parola chiave OUTPUT per qualsiasi dichiarazione di parametro specifica.
Recupero di informazioni sulle stored procedure
Per visualizzare la definizione di una stored procedure Transact-SQL, utilizzare la vista del catalogo sys.sql_modules nel database in cui esiste la procedura.
Ad esempio:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';
[!NOTA]
Il testo di una stored procedure creata con l'opzione ENCRYPTION non può essere visualizzato tramite la vista del catalogo sys.sql_modules.
Per ottenere un report sugli oggetti a cui una procedura fa riferimento, eseguire una query sulla vista del catalogo sys.sql_expression_dependencies vista del catalogo oppure utilizzare sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities.
Per visualizzare informazioni su stored procedure CLR, utilizzare la vista del catalogo sys.assembly_modules nel database in cui esiste la procedura.
Per visualizzare informazioni sui parametri definiti in una stored procedure, utilizzare la vista del catalogo sys.parameters nel database in cui esiste la procedura.
Risoluzione dei nomi posticipata
È possibile creare stored procedure che fanno riferimento a tabelle non ancora esistenti. In fase di creazione viene eseguito solo un controllo della sintassi. La stored procedure non viene compilata fino alla prima esecuzione ed è solo durante la compilazione che vengono risolti tutti gli oggetti a cui viene fatto riferimento nella stored procedure. Pertanto, è possibile creare una stored procedure che fa riferimento a tabelle non ancora esistenti, purché la sintassi sia corretta. Se, tuttavia, le tabelle a cui viene fatto riferimento non esistono in fase di esecuzione, la stored procedure avrà esito negativo. Per ulteriori informazioni, vedere Risoluzione dei nomi posticipata e compilazione.
Esecuzione di stored procedure
Quando si esegue una stored procedure definita dall'utente, in un batch oppure all'interno di un modulo quale una stored procedure o una funzione definita dall'utente, è consigliabile utilizzare per la stored procedure un nome completo comprensivo del nome dello schema.
È possibile specificare i valori dei parametri se una stored procedure viene scritta in modo da accettarli. Il valore specificato può essere una costante o una variabile. Non è possibile specificare un nome di funzione come valore di un parametro. Le variabili possono essere definite dall'utente oppure di sistema, ad esempio @@SPID.
Per ulteriori informazioni, vedere Esecuzione di stored procedure (Motore di database).
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 di Motore di database, può essere riutilizzato nelle successive esecuzioni della stored procedure. Per ulteriori informazioni, vedere Caching e riutilizzo del piano di esecuzione.
Parametri che utilizzano il tipo di dati cursor
Nelle stored procedure Transact-SQL è possibile utilizzare il tipo di dati cursor solo per i parametri OUTPUT. Se per un parametro viene specificato il tipo di dati cursor, entrambi i parametri VARYING e OUTPUT risultano obbligatori. Se per un parametro viene specificata la parola chiave VARYING, è necessario impostare il tipo di dati cursor e specificare la parola chiave OUTPUT. Per ulteriori informazioni, vedere Utilizzo del tipo di dati cursor in un parametro OUTPUT.
Stored procedure temporanee
In Motore di database sono supportati due tipi di procedure temporanee: locali e globali. Una procedura temporanea locale è visibile solo nella connessione in cui è stata creata. Una procedura temporanea globale è disponibile per tutte le connessioni. Le procedure temporanee locali vengono eliminate automaticamente al termine della sessione corrente, mentre le procedure temporanee globali vengono eliminate al termine dell'ultima sessione in cui la procedura viene utilizzata. Per ulteriori informazioni, vedere Creazione di stored procedure (Motore di database).
Esecuzione automatica di stored procedure
È possibile eseguire automaticamente una o più stored procedure all'avvio di SQL Server. Le stored 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 Esecuzione automatica di stored procedure.
Nidificazione di stored procedure
Le stored procedure possono essere nidificate, il che significa che una stored procedure può chiamare un'altra stored procedure. Il livello di nidificazione aumenta all'avvio della procedura chiamata e diminuisce al termine dell'esecuzione della procedura chiamata. Le stored procedure supportano fino a 32 livelli di nidificazione. Per ulteriori informazioni, vedere Nidificazione di stored procedure.
Per stimare la dimensione di una stored procedure compilata, utilizzare i contatori di Performance Monitor seguenti.
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 SQL ad hoc e preparate, procedure, trigger e così via.
Per ulteriori informazioni, vedere Oggetto Plan Cache di SQL Server.
Limitazioni di <sql_statement>
In una stored procedure è 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 stored procedure, dopodiché viene ripristinata l'impostazione precedente.
In una stored procedure i nomi di oggetto utilizzati in tutte le istruzioni DDL (Data Definition Language), ad esempio istruzioni CREATE, ALTER o DROP, istruzioni DBCC, istruzioni EXECUTE e SQL dinamiche, devono includere il nome dello schema dell'oggetto se la stored procedure verrà utilizzata da utenti diversi dal relativo proprietario. Per ulteriori informazioni, vedere Progettazione di stored procedure (Motore di database).
Autorizzazioni
È necessario disporre dell'autorizzazione CREATE PROCEDURE per il database e dell'autorizzazione ALTER per lo schema in cui la procedura viene creata.
Per le stored procedure CLR è necessario essere proprietari dell'assembly a cui viene fatto riferimento in <method_specifier> oppure disporre dell'autorizzazione REFERENCES per tale assembly.
Esempi
A. Utilizzo di una procedura semplice
La stored procedure seguente restituisce tutti i dipendenti (per cui vengono indicati sia il nome che il cognome), le relative posizioni e i nomi dei reparti di appartenenza da una vista. In questa stored procedure non viene utilizzato alcun parametro.
USE AdventureWorks;
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 stored procedure 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. Utilizzo di una procedura semplice con parametri
La stored procedure seguente restituisce solo il dipendente specificato (per cui vengono indicati sia il nome che il cognome), la relativa posizione e il nome del reparto di appartenenza da una vista. Questa stored procedura accetta corrispondenze esatte per i parametri passati.
USE AdventureWorks;
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 stored procedure 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';
C. Utilizzo di una procedura semplice con parametri jolly
La stored procedure seguente restituisce solo i dipendenti specificati (per cui vengono indicati sia il nome che il cognome), le relative posizioni e i reparti di appartenenza da una vista. Lo schema di questa stored procedure corrisponde ai parametri passati oppure, se non è stato specificato alcun parametro, ai parametri predefiniti (cognomi che iniziano con la lettera D).
USE AdventureWorks;
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 stored procedure uspGetEmployees2 può essere eseguita in molte combinazioni diverse. Di seguito sono riportate alcune combinazioni:
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%';
D. Restituzione di più di un set di risultati
La stored procedure seguente restituisce due set di risultati.
USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO
E. Utilizzo di parametri OUTPUT
Nell'esempio seguente viene creata la stored procedure 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.
USE AdventureWorks;
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.
F. 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 AdventureWorks;
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.VendorID = pv.VendorID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
GO
G. Utilizzo dell'opzione WITH ENCRYPTION
Nell'esempio seguente viene creata la stored procedure HumanResources.uspEncryptThis.
USE AdventureWorks;
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 EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
FROM HumanResources.Employee;
GO
L'opzione WITH ENCRYPTION impedisce che venga restituita la definizione della stored procedure, 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 AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
Set di risultati:
definition
----------------------
NULL
(1 row(s) affected)
H. Utilizzo della risoluzione dei nomi posticipata
Nell'esempio seguente viene creata la procedura uspProc1 che utilizza la risoluzione dei nomi posticipata. La stored procedure viene creata anche se la tabella a cui viene fatto riferimento non esiste in fase di compilazione. La tabella deve però esistere quando viene eseguita la procedura.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
SET NOCOUNT ON;
SELECT column1, column2 FROM table_does_not_exist
GO
Per verificare che la stored procedure sia stata creata, eseguire la query seguente:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');
Set di risultati:
definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
SELECT column1, column2 FROM table_does_not_exist
(1 row(s) affected)
I. Utilizzo della clausola EXECUTE AS
Nell'esempio seguente viene illustrato l'utilizzo della clausola EXECUTE AS per specificare il contesto di protezione in cui può essere eseguita una stored procedure. In questo esempio l'opzione CALLER specifica che la procedura può essere eseguita nel contesto dell'utente che la chiama.
USE AdventureWorks;
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 'Credit Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.VendorID = pv.VendorID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
J. Creazione di una stored procedure CLR
Nell'esempio seguente viene creata la stored procedure GetPhotoFromDB che fa riferimento al metodo GetPhotoFromDB della classe LargeObjectBinary nell'assembly HandlingLOBUsingCLR . Prima di creare la stored procedure, viene registrato l'assembly HandlingLOBUsingCLR 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
K. Utilizzo di un parametro OUTPUT di tipo cursore
I parametri OUTPUT di tipo cursore vengono utilizzati per passare nuovamente al batch, alla stored procedure o al trigger chiamante un cursore locale rispetto a una stored procedure.
Creare innanzitutto la procedura che dichiara e quindi apre un cursore nella tabella Currency:
USE AdventureWorks;
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 dichiara una variabile locale di cursore, esegue la procedura per assegnare il cursore alla variabile locale e quindi recupera le righe dal cursore.
USE AdventureWorks;
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
Vedere anche