Creazione di stored procedure (Motore di database)
Per creare stored procedure, è possibile utilizzare l'istruzione Transact-SQL CREATE PROCEDURE.
Prima di creare una stored procedure, considerare quanto segue:
Le istruzioni CREATE PROCEDURE non possono essere utilizzate in combinazione con altre istruzioni SQL in un singolo batch.
Per creare procedure, è necessario disporre dell'autorizzazione CREATE PROCEDURE per il database e dell'autorizzazione ALTER per lo schema in cui verrà creata la procedura. Nel caso di stored procedure CLR, è necessario essere proprietari dell'assembly cui viene fatto riferimento in <method_specifier> oppure disporre dell'autorizzazione REFERENCES per tale assembly.
Le stored procedure sono oggetti definiti a livello di schema, i cui nomi devono essere conformi alle regole di denominazione degli identificatori.
È possibile creare una stored procedure soltanto nel database corrente.
Durante la creazione di una stored procedure è necessario specificare:
Tutti i parametri di input e di output della procedura o del batch che esegue la chiamata.
Le istruzioni di programmazione che eseguono le operazioni nel database, tra cui la chiamata di altre procedure.
Il valore di stato restituito alla procedura o al batch che esegue la chiamata per indicare l'esito positivo o negativo (e il motivo dell'esito negativo).
Tutte le istruzioni di gestione degli errori necessarie per individuare e gestire possibili errori.
È possibile specificare nella stored procedure nuove funzioni di gestione degli errori, ad esempio ERROR_LINE e ERROR_PROCEDURE. Per ulteriori informazioni, vedere Utilizzo di TRY...CATCH in Transact-SQL.
Denominazione di stored procedure
È consigliabile non utilizzare sp_ come prefisso del nome durante la creazione di stored procedure. Il prefisso sp_ viene infatti utilizzato in SQL Server per indicare stored procedure di sistema. Potrebbero quindi verificarsi conflitti tra il nome scelto e quello di una stored procedure di sistema resa disponibile in futuro. Se per l'applicazione vengono utilizzati riferimenti a nomi completi non associati a schema e si verifica un conflitto tra il nome della procedura e quello di una procedura di sistema, l'esecuzione dell'applicazione verrà interrotta perché il nome viene associato a quello della procedura di sistema e non a quello della procedura dell'utente.
Una stored procedure definita dall'utente non verrà mai eseguita se presenta lo stesso nome di una stored procedure di sistema ed è non completo oppure se è inclusa nello schema dbo, pertanto verrà sostituita dalla stored procedure di sistema. Questo funzionamento è illustrato nell'esempio seguente.
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO
L'utilizzo di un qualificatore di schema esplicito offre inoltre alcuni vantaggi in termini di prestazioni. La risoluzione dei nomi è leggermente più rapida se il Motore di database non deve eseguire la ricerca in più schemi per individuare la procedura. Per ulteriori informazioni, vedere Esecuzione di una stored procedure.
Stored procedure temporanee
Le stored procedure temporanee private e globali possono essere create in modo analogo alle tabelle temporanee aggiungendo i prefissi # e ## al nome della procedura. # specifica una stored procedure temporanea locale, mentre ## indica una stored procedure temporanea globale. Queste procedure vengono eliminate alla chiusura di SQL Server.
Le stored procedure temporanee sono utili quando si stabilisce una connessione a versioni precedenti di SQL Server che non supportano il riutilizzo dei piani di esecuzione per istruzioni o batch Transact-SQL. È consigliabile che le applicazioni che stabiliscono la connessione a SQL Server 2000 e versioni successive utilizzino la stored procedure di sistema sp_executesql anziché le stored procedure temporanee. Una procedura temporanea privata può essere eseguita soltanto dalla connessione da cui è stata creata e viene automaticamente eliminata alla chiusura della connessione.
Qualsiasi connessione può eseguire una stored procedure temporanea globale. Una stored procedure temporanea globale esiste fino al momento in cui la connessione utilizzata dall'utente che ha creato la procedura viene chiusa e le versioni della procedura in esecuzione da altre connessioni vengono completate. Dopo la chiusura della connessione utilizzata per creare la procedura non sono consentite altre esecuzioni della stored procedure temporanea globale. È consentito soltanto il completamento delle connessioni che hanno già avviato l'esecuzione della stored procedure.
Una stored procedure senza prefisso # o ## creata direttamente nel database tempdb verrà automaticamente eliminata alla chiusura di SQL Server perché il database tempdb viene ricreato a ogni avvio di SQL Server. Le procedure create direttamente in tempdb sono disponibili anche dopo l'interruzione della connessione di creazione.
Nota
L'utilizzo massiccio di stored procedure temporanee può provocare una contesa nelle tabelle di sistema in tempdb e influire negativamente sulle prestazioni. È consigliabile utilizzare sp_executesql. Con questa procedura infatti i dati non vengono archiviati nelle tabelle di sistema e non si verifica pertanto alcuna contesa.
Non è possibile creare stored procedure CLR temporanee.
Esempi
A. Utilizzo di una procedura semplice con un'istruzione SELECT complessa
La stored procedure seguente restituisce da una vista tutti i dipendenti (di cui è stato indicato nome e cognome), il relativo tipo e il nome del reparto, senza utilizzare parametri.
USE AdventureWorks2008R2;
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 da una vista solo la dipendente specificata (di cui è stato indicato nome e cognome), la relativa mansione e il nome del reparto. Questa stored procedura accetta corrispondenze esatte per i parametri passati.
USE AdventureWorks2008R2;
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. La stored procedure individua le corrispondenze in base al modello specificato nei parametri passati. Se non è stato specificato alcun parametro, utilizza i valori predefiniti, ovvero i cognomi che iniziano con la lettera D.
USE AdventureWorks2008R2;
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. alcune delle quali sono riportate di seguito:
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. Utilizzo di parametri OUTPUT
Nell'esempio seguente viene creata la stored procedure uspGetList, che restituisce un elenco di prodotti i cui prezzi non superano un importo specificato. Nell'esempio viene illustrato l'utilizzo di più istruzioni SELECT e di più parametri OUTPUT. I parametri OUTPUT consentono a una procedura esterna, a un batch o a più istruzioni Transact-SQL di accedere a un valore impostato durante l'esecuzione della procedura.
USE AdventureWorks2008R2;
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 di 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 durante la creazione della procedura e durante l'utilizzo della variabile. Non è necessario che il nome del parametro e il nome della variabile corrispondano. Il tipo di dati e la posizione del parametro invece devono corrispondere, a meno 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)))+'.'
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.
Vedere anche