Creazione di stored procedure (Motore di database)

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.

Non è possibile creare stored procedure CLR temporanee.

Esempi

A. Utilizzo di una procedura semplice con un'istruzione SELECT complessa

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

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

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

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

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

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

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

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)))+'.'

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á

Riferimento

Concetti

Altre risorse