Creazione di stored procedure (Motore di database)

  • Articolo

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.
    Microsoft SQL Server 2005 include nuove funzioni di gestione degli errori, ad esempio ERROR_LINE e ERROR_PROCEDURE, che è possibile specificare nella stored 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 AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
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 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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
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. La procedura accetta corrispondenze esatte con 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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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 da una vista solo i dipendenti specificati (di cui è stato indicato nome e cognome), le relative mansioni e i reparti. 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 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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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. Per l'esempio vengono utilizzate più istruzioni SELECT e 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 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 di prodotti Adventure Works (biciclette) che costano meno di $ 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 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

Attività

Procedura: Creazione di una stored procedure (SQL Server Management Studio)

Concetti

Rollback e commit in stored procedure e trigger
Creazione di stored procedure CLR
Esecuzione di stored procedure (Motore di database)
Modifica di stored procedure
Ricompilazione di stored procedure
Visualizzazione di stored procedure
Eliminazione di stored procedure

Altre risorse

CREATE PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
Clausola EXECUTE AS (Transact-SQL)
REVERT (Transact-SQL)
Cambi di contesto
Specifica dei parametri
Restituzione di dati da una stored procedure

Guida in linea e informazioni

Assistenza su SQL Server 2005