Condividi tramite

  • Articolo

Esecuzione di stored procedure (Motore di database)

Per eseguire una stored procedure, utilizzare l'istruzione Transact-SQL EXECUTE. Se la stored procedure è la prima istruzione del batch, è possibile eseguirla senza utilizzare la parola chiave EXECUTE.

Esecuzione di stored procedure di sistema

Le stored procedure di sistema iniziano con i caratteri sp_. Sono archiviate fisicamente nel database delle risorse, ma a livello logico compaiono nello schema sys di tutti i database di sistema e definiti dall'utente presenti nell'istanza di SQL Server. Le stored procedure di sistema possono essere eseguite da qualsiasi database senza doverne specificare il nome completo. Un nome senza specificazione dello schema può essere un nome composto da una parte, come sp_someproc, oppure un nome composto da tre parti, come somedb..sp_someproc, nel quale la seconda parte, ovvero il nome dello schema, non è specificata.

È consigliabile specificare lo schema in tutti i nomi di stored procedure di sistema con il nome dello schema sys per evitare conflitti. Nell'esempio seguente viene illustrato il metodo consigliato per l'esecuzione di una stored procedure di sistema.

EXEC sys.sp_who;

Nell'esempio seguente vengono illustrati metodi compatibili con le versioni precedenti per l'esecuzione delle stored procedure di sistema.

[!NOTA]

I metodi per l'esecuzione delle stored procedure di sistema seguenti verranno rimossi a partire da una delle prossime versioni di SQL Server. Evitare di utilizzare questi metodi in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente li utilizzano.

EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;

Individuazione delle corrispondenze nelle regole di confronto del database

SQL Server 2008 utilizza per la ricerca dei nomi delle procedure di sistema corrispondenti le regole di confronto del database che ha eseguito la chiamata. Nei nomi delle procedure di sistema è pertanto necessario utilizzare sempre la corretta combinazione di maiuscole e minuscole. Ad esempio, il codice seguente, se eseguito nel contesto di un database con regole di confronto con distinzione tra maiuscole e minuscole, genererà un errore:

exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

Utilizzare le viste del catalogo sys.system_objects e sys.system_parameters per visualizzare i nomi esatti delle stored procedure di sistema.

Esecuzione di stored procedure estese di sistema

Le stored procedure estese di sistema iniziano con i caratteri xp_. Sono archiviate fisicamente nel database delle risorse, ma a livello logico compaiono nello schema sys di tutti i database di sistema e definiti dall'utente presenti nell'istanza di SQL Server. Nell'esempio seguente viene illustrato il metodo consigliato per l'esecuzione di una stored procedure estesa di sistema.

EXEC sys.xp_subdirs 'c:\';

Esecuzione di stored procedure definite dall'utente

Quando si esegue una stored procedure definita dall'utente nell'ambito di un batch o all'interno di un modulo, ad esempio una stored procedure o una funzione definita dall'utente, è consigliabile specificare nel nome della stored procedure almeno il nome dello schema.

Nell'esempio seguente viene illustrato il metodo consigliato per l'esecuzione di una stored procedure definita dall'utente.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

-oppure-

EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
GO

Se si specifica una stored procedure non qualificata definita dall'utente, il Motore di database cerca la procedura nell'ordine seguente:

  • Schema sys del database corrente.

  • Lo schema predefinito del chiamante se eseguito in un batch o in SQL dinamico. Oppure, se il nome della procedura non qualificato viene visualizzato all'interno del corpo di un'altra definizione di procedura, la ricerca viene eseguita nello schema che include quest'ultima procedura subito dopo. Per ulteriori informazioni sugli schemi predefiniti, vedere l'argomento relativo alla separazione degli schemi utente.

  • Schema dbo nel database corrente.

Nota importanteImportante

Se una stored procedure creata dall'utente ha lo stesso nome di una stored procedure di sistema, la stored procedure creata dall'utente non verrà mai eseguita se si utilizza il riferimento a un nome senza specificazione dello schema. Per ulteriori informazioni, vedere Creazione di stored procedure (Motore di database).

Specifica dei parametri

È possibile specificare i valori dei parametri se una stored procedure viene scritta in modo da accettarli.

Il valore può essere una costante o una variabile. Non è possibile specificare come valore di parametro il nome di una funzione. Le variabili possono essere definite dall'utente oppure di sistema, ad esempio @@spid.

Nell'esempio seguente viene illustrato il passaggio di valori di parametro alla stored procedure uspGetWhereUsedProductID. La procedura prevede valori per due parametri di input, un ID di prodotto e una data. Negli esempi viene illustrata la procedura per passare i parametri come costanti e variabili e per utilizzare una variabile per il passaggio del valore di una funzione.

USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Se si desidera specificare i parametri in un ordine diverso rispetto a quello in cui sono definiti nella stored procedure, è necessario assegnare loro un nome. Per ulteriori informazioni, vedere Impostazione di un nome di parametro.

Per specificare che un parametro deve restituire un valore al programma che esegue la chiamata, utilizzare la parola chiave OUTPUT. Per ulteriori informazioni, vedere Impostazione della direzione di un parametro.

Specifica dell'ordine dei parametri

Se i parametri vengono specificati nel formato **@parameter =**value, è possibile definirli in qualsiasi ordine. È inoltre possibile omettere i parametri di cui sono stati specificati i valori predefiniti. Se un parametro viene specificato nel formato @parameter = value, è necessario definire tutti i parametri successivi in questo modo. Se i parametri non vengono specificati nel formato **@parameter =**value, è necessario definirli nell'ordine specificato nell'istruzione CREATE PROCEDURE.

Quando si esegue una stored procedure, il server rifiuta i parametri non inclusi nell'elenco di parametri durante la fase di creazione della procedura. I parametri passati per riferimento (passando esplicitamente il nome di parametro) non vengono accettati se il nome di parametro non corrisponde.

Utilizzo dei valori predefiniti nei parametri

Sebbene sia possibile omettere i parametri di cui sono stati specificati i valori predefiniti, è possibile troncare soltanto l'elenco di parametri. Se, ad esempio, una stored procedure include cinque parametri, è possibile omettere il quarto e il quinto parametro, ma non è possibile omettere il quarto e includere il quinto, a meno che i parametri non vengano specificati nel formato **@parameter =**value.

Il valore predefinito di un parametro, se definito per il parametro nella stored procedure, viene utilizzato quando:

  • Non viene specificato alcun valore per il parametro quando la stored procedure viene eseguita.

  • Viene specificata la parola chiave DEFAULT come valore per il parametro.