CREATE PROCEDURE (Transact-SQL)

Sintassi

--Transact-SQL Stored Procedure Syntax
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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

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

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Argomenti

• 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. Per ulteriori informazioni sugli schemi, vedere Separazione tra schema e utente.

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

  Evitare l'utilizzo del prefisso sp_ per la denominazione delle procedure. Questo prefisso viene utilizzato 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. Per ulteriori informazioni, vedere Progettazione di stored procedure (Motore di database).

  Le procedure temporanee locali o globali possono essere create utilizzando un simbolo di cancelletto (#) prima del procedure_name (#procedure_name) per le procedure temporanee locali e due simboli di cancelletto per le procedure temporanee globali (##procedure_name). Una procedura temporanea locale è visibile solo alla connessione da cui è stata creata e, alla chiusura di quest'ultima, viene eliminata. Una procedura temporanea globale è disponibile per tutte le connessioni e viene eliminata al termine dell'ultima sessione tramite la procedura. Non è possibile specificare nomi temporanei per le procedure CLR.

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

• **;**number
  Numero intero facoltativo utilizzato per raggruppare le procedure con lo stesso nome. Tali procedure possono essere eliminate contemporaneamente tramite un'istruzione DROP PROCEDURE.

  Nota

  Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa caratteristica 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 definiti dall'utente xml o CLR né possono essere utilizzate in una guida di piano.

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

  È possibile specificare 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 in una procedura sono contenuti parametri con valori di tabella e nella chiamata non è disponibile alcun parametro, viene passata una tabella vuota. I parametri possono rappresentare solo espressioni costanti, non nomi di tabelle, di colonne o nomi di altri oggetti di 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 il tipo di dati.

  Linee guida relative al tipo di dati per le procedure Transact-SQL:

  • Tutti i tipi di dati Transact-SQL possono essere utilizzati come parametri.

  • Per creare parametri con valori di tabella è possibile utilizzare il tipo di tabella definito dall'utente. I parametri con valori di tabella possono essere solo parametri di input e insieme a essi deve essere associata la parola chiave READONLY. Per ulteriori informazioni, vedere 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 relative al tipo di dati per procedure CLR:

  • Tutti i tipi di dati nativi di SQL Server per cui è presente un equivalente nel codice gestito possono essere utilizzati come parametri. 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).

  • I tipi di dati con valori di tabella o cursor non possono essere utilizzati 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. Viene utilizzato solo con parametri di tipo 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 utilizzare la parola chiave LIKE quando si passa il parametro nella procedura. Vedere l'esempio C riportato di seguito.

  I valori predefiniti vengono registrati nella colonna sys.parameters.default solo per procedure CLR. Tale colonna sarà NULL per i parametri di procedure Transact-SQL.

• OUT | OUTPUT
  Indica che si tratta di un parametro di output. Utilizzare i parametri OUTPUT per restituire i valori al chiamante della procedura. I parametri text, ntext e image non posso essere utilizzati 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 di 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, pertanto verrà compilato a ogni esecuzione. Per ulteriori informazioni sui motivi della ricompilazione forzata, vedere Ricompilazione di stored procedure. Questa opzione non può essere utilizzata per 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, utilizzare il suggerimento per la query RECOMPILE nella definizione della query. Per ulteriori informazioni, vedere Hint per la query (Transact-SQL).

• ENCRYPTION
  Indica che in SQL Server il testo originale dell'istruzione CREATE PROCEDURE verrà convertito in un formato offuscato. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo in SQL Server. Il testo offuscato non può essere recuperato da utenti che non hanno accesso a tabelle di sistema o da file del database. Tuttavia, tale testo sarà 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, 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 le procedure CLR.

  Le procedure create con questa opzione non possono essere pubblicate durante la replica di SQL Server.

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

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

• FOR REPLICATION
  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 utilizzata 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 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.

• { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
  Una o più istruzioni Transact-SQL che includono il corpo della procedura. Per racchiudere le istruzioni è possibile utilizzare 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
  Specifica il metodo di un assembly .NET Framework a cui una procedura CLR deve fare riferimento. 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.

  Per impostazione predefinita, il codice CLR non può essere eseguito in SQL Server. È 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. Per abilitare questa opzione, utilizzare sp_configure.

Procedure consigliate

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

• Utilizzare l'istruzione SET NOCOUNT ON come prima istruzione nel corpo della procedura, ovvero posizionarla subito dopo la parola chiave AS. In questo modo vengono disattivati i messaggi restituiti al client da SQL Server dopo l'esecuzione delle istruzioni SELECT, INSERT, UPDATE, MERGE e DELETE. Le prestazioni generali del database e dell'applicazione vengono migliorate eliminando questo overhead di rete. Per ulteriori informazioni, vedere SET NOCOUNT (Transact-SQL).

• Utilizzare i nomi degli schemi quando si crea o si fa riferimento agli oggetti di database nella procedura. La risoluzione dei nomi degli oggetti da parte del Motore di database richiederà un minor tempo di elaborazione se la ricerca non deve essere effettuata in più schemi. Inoltre sarà possibile evitare problemi di autorizzazioni e accesso causati dall'assegnazione dello schema predefinito di un utente quando gli oggetti vengono creati senza specificare lo schema. Per ulteriori informazioni, vedere Separazione tra schema e utente.

• 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'utilizzo 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 di 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 utilizzando il set di dati più piccolo possibile. Inviare solo i dati essenziali all'applicazione client. Tale operazione è più efficace dell'invio di dati aggiuntivi nella rete, nonché dell'imposizione all'applicazione client di utilizzare set di risultati inutilmente grandi.

• Utilizzare le transazioni esplicite tramite BEGIN/END TRANSACTION mantenendole più corte possibili. Transazioni lunghe implicano un blocco dei record più lungo e un rischio maggiore di deadlock. Per ulteriori informazioni, vedere Utilizzo di blocchi e controllo delle versioni delle righe, Compatibilità tra blocchi (Motore di database) o Livelli di isolamento nel motore di database.

• Per la gestione degli errori all'interno di una procedura utilizzare 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. Per ulteriori informazioni, vedere Utilizzo di TRY...CATCH in Transact-SQL.

• Utilizzare 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.

• Utilizzare 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.

• Utilizzare 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 utilizzano le funzioni di aggregazione. Per ulteriori informazioni, vedere Condizioni di ricerca per confronti di valori Null e Valori Null.

• Utilizzare l'operatore UNION ALL invece degli operatori UNION oppure OR, a meno che non siano necessari valori distinct. L'operatore UNION ALL richiede un minor overhead di elaborazione poiché i duplicati non vengono esclusi dal set di risultati.

Osservazioni generali

Non è prevista una dimensione massima predefinita per una procedura.

Le variabili presenti nella procedura possono essere definite dall'utente o possono essere variabili 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 archiviato nell'apposita cache del Motore di database, può essere riutilizzato nelle successive esecuzioni della procedura. Per ulteriori informazioni, vedere Memorizzazione nella cache e riutilizzo del piano di esecuzione o Esecuzione di stored procedure e trigger.

È possibile eseguire automaticamente una o più procedure all'avvio di SQL Server. Le 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 di stored procedure (Motore di database).

Le procedure vengono nidificate 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 nidificazione. 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. Per restituire il livello di nidificazione dell'esecuzione della stored procedure corrente è possibile utilizzare la funzione @@NESTLEVEL.

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 utilizzate 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. Per ulteriori informazioni sulle opzioni SET, vedere Opzioni SET.

Limitazioni e restrizioni

L'istruzione CREATE PROCEDURE non può essere utilizzata in combinazione con altre istruzioni Transact-SQL all'interno di un singolo batch.

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

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. Pertanto, è 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 avrà esito negativo. Per ulteriori informazioni, vedere Risoluzione dei nomi posticipata e compilazione.

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.

-- 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. Per ulteriori informazioni, vedere Gestione degli errori nelle stored procedure remote tra server.

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.

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

Metadati

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

Per stimare la dimensione di una procedura compilata, utilizzare i contatori di Performance Monitor Percentuale riscontri cache, Pagine cache e Conteggio oggetti cache. Per ulteriori informazioni, vedere Oggetto Plan Cache di SQL Server.

Sicurezza

Autorizzazioni

Esempi

Sintassi di base

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

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

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

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

Passaggio di parametri

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

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

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

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%';

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

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

USE AdventureWorks2008R2;
GO

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

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

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

Modifica dei dati tramite una stored procedure

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

Gestione degli errori

USE AdventureWorks2008R2;
GO

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;

/* 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;

DROP PROCEDURE Production.uspDeleteWorkOrder;

Offuscamento della definizione delle procedure

USE AdventureWorks2008R2;
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 BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

Ricompilazione forzata della procedura

USE AdventureWorks2008R2;
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.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

Impostazione del contesto di sicurezza

USE AdventureWorks2008R2;
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 '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

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

Vedere anche

Riferimento

Concetti

Altre risorse