sp_executesql (Transact-SQL)

Data aggiornamento: 5 dicembre 2005

Esegue un'istruzione o un batch Transact-SQL che può essere riutilizzato più volte o che è stato creato in modo dinamico. L'istruzione o il batch Transact-SQL può contenere parametri incorporati.

Convenzioni della sintassi Transact-SQL

Sintassi

sp_executesql [ @stmt = ] stmt
[ 
    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } 
     {, [ @param1 = ] 'value1' [ ,...n ] }
]

Argomenti

• [ @stmt = ] stmt
  Stringa Unicode contenente un'istruzione o un batch Transact-SQL. stmt deve essere una costante o una variabile Unicode. Non sono consentite le espressioni Unicode più complesse, ad esempio per il concatenamento di due stringhe tramite l'operatore +. Le costanti di tipo carattere non sono consentite. Se si specifica una costante Unicode, è necessario aggiungere il prefisso N. La costante Unicode N'sp_who', ad esempio, è valida, mentre la costante di tipo carattere 'sp_who' non lo è. Le dimensioni massime della stringa dipendono dalla memoria disponibile nel server di database. Nei server a 64 bit, le dimensioni della stringa sono limitate a 2 GB, ovvero alle dimensioni massime di nvarchar(max).

  [!NOTA] stmt può contenere parametri nello con lo stesso formato di un nome di variabile, ad esempio: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

  A ogni parametro incluso in stmt deve corrispondere una voce nell'elenco delle definizioni dei parametri @params e nell'elenco dei valori dei parametri.

• [ @params = ] **N'@**parameter_namedata_type[ ,... n ] '
  Stringa contenente le definizioni di tutti i parametri che sono stati incorporati in stmt. La stringa deve essere una costante o una variabile Unicode. Ogni definizione di parametro è costituita da un nome di parametro e da un tipo di dati. n è un segnaposto che indica definizioni di parametri aggiuntive. Ogni parametro specificato in stmt deve essere definito in @params. Se l'istruzione o il batch Transact-SQL in stmt non contiene parametri, @params non è necessario. Il valore predefinito per questo parametro è NULL.

• [ **@**param1 = ] 'value1'
  Valore per il primo parametro definito nella stringa di parametri. Il valore può essere una costante o una variabile Unicode. È necessario che sia disponibile un valore di parametro per ogni parametro incluso in stmt. I valori non sono necessari se l'istruzione o il batch Transact-SQL in stmt è privo di parametri.

• [ OUT | OUTPUT ]
  Specifica che si tratta di un parametro di output. I parametri di tipo text, ntext e image possono essere utilizzati come parametri OUTPUT, a meno che non si tratti di una procedura CLR. Un parametro di output che utilizza la parola chiave OUTPUT può essere il segnaposto di un cursore, a meno che la procedura non sia una procedura CLR.

• n
  Segnaposto per i valori di parametri aggiuntivi. I valori possono essere solo costanti o variabili. Non sono consentite espressioni più complesse quali funzioni o espressioni create tramite operatori.

Set di risultati

Restituisce i set di risultati di tutte le istruzioni SQL definite nella stringa SQL.

Osservazioni

La stored procedure sp_executesql funziona in modo analogo a EXECUTE per quanto riguarda i batch, l'ambito dei nomi e il contesto del database. L'istruzione o il batch Transact-SQL nel parametro stmt di sp_executesql non viene compilato finché non viene eseguita l'istruzione sp_executesql. Il contenuto di stmt viene quindi compilato ed eseguito come piano di esecuzione distinto dal piano di esecuzione del batch che ha chiamato sp_executesql. Il batch di sp_executesql non può fare riferimento a variabili dichiarate nel batch che chiama sp_executesql. I cursori o le variabili locali del batch sp_executesql non sono visibili per il batch che chiama sp_executesql. Le modifiche apportate al contesto del database durano solo fino al termine dell'esecuzione dell'istruzione sp_executesql.

È possibile utilizzare sp_executesql anziché stored procedure per eseguire un'istruzione Transact-SQL più volte quando l'unica variazione è costituita dalla modifica dei valori dei parametri. Poiché l'istruzione Transact-SQL stessa rimane costante e cambiano solo i valori dei parametri, è probabile che Query Optimizer di SQL Server riutilizzi il piano di esecuzione generato per la prima esecuzione.

[!NOTA] Per ottimizzare le prestazioni, utilizzare nomi di oggetto completi nella stringa dell'istruzione.

sp_executesql supporta l'impostazione dei valori dei parametri in modo autonomo rispetto alla stringa Transact-SQL, come illustrato nell'esempio seguente.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

È inoltre possibile utilizzare parametri di output con sp_executesql. Nell'esempio seguente un titolo professionale viene recuperato dalla tabella AdventureWorks.HumanResources.Employee e restituito nel parametro di output @max_title.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

La possibilità di sostituire i parametri in sp_executesql offre i vantaggi seguenti rispetto all'utilizzo dell'istruzione EXECUTE per l'esecuzione di una stringa:

• Dato che il testo effettivo dell'istruzione Transact-SQL nella stringa sp_executesql rimane invariato tra un'esecuzione e la successiva, Query Optimizer cerca probabilmente di far corrispondere l'istruzione Transact-SQL nella seconda esecuzione con il piano di esecuzione generato per la prima esecuzione. Di conseguenza SQL Server non deve compilare la seconda istruzione.
• La stringa Transact-SQL viene generata una sola volta.
• Il parametro integer viene specificato nel formato nativo. Non è necessario eseguire il cast a Unicode.

Autorizzazioni

È richiesta l'appartenenza al ruolo public.

Valori restituiti

0 (esito positivo) o valore diverso da zero (esito negativo)

Esempi

A. Esecuzione di un'istruzione SELECT semplice

Nell'esempio seguente viene creata ed eseguita un'istruzione SELECT semplice che contiene un parametro incorporato denominato @level.

EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

B. Esecuzione di una stringa creata in modo dinamico

Nell'esempio seguente viene illustrato l'utilizzo di sp_executesql per l'esecuzione di una stringa creata in modo dinamico. La stored procedure di esempio consente di inserire dati in un set di tabelle utilizzate per il partizionamento dei dati relativi alle vendite annuali. È disponibile una sola tabella per ogni mese dell'anno nel formato seguente:

CREATE TABLE May1998Sales
    (OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate  datetime NULL
        CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth int
        CHECK (OrderMonth = 5),
    DeliveryDate datetime  NULL,
        CHECK (DATEPART(mm, OrderDate) = OrderMonth)
    )

Questa stored procedure di esempio crea in modo dinamico ed esegue un'istruzione INSERT per l'inserimento di nuovi ordini nella tabella corretta. La data dell'ordine viene utilizzata per creare il nome della tabella che deve contenere i dati, quindi il nome viene incorporato in un'istruzione INSERT.

[!NOTA] Si tratta di un semplice esempio di utilizzo di sp_executesql. Non è previsto alcun controllo degli errori o delle regole business, ad esempio non viene verificato che i numeri di ordine siano univoci tra le tabelle.

CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
     @OrderMonth, @PrmDeliveryDate

GO

L'utilizzo di sp_executesql in questa procedura è più funzionale rispetto all'utilizzo di EXECUTE per l'esecuzione di una stringa. Quando si utilizza sp_executesql, vengono generate solo 12 versioni della stringa INSERT, una per ogni tabella mensile. Con l'istruzione EXECUTE ogni stringa INSERT è univoca, in quanto i valori dei parametri sono diversi. Sebbene entrambi i metodi generino lo stesso numero di batch, data la similarità delle stringhe INSERT generate da sp_executesql è più probabile che Query Optimizer riutilizzi i piani di esecuzione.

C. Utilizzo del parametro OUTPUT

Nell'esempio seguente viene utilizzato un parametro OUTPUT per archiviare il set di risultati generato dall'istruzione SELECT nel parametro @SQLString. Vengono quindi eseguite due istruzioni SELECT che utilizzano il valore del parametro OUTPUT.

USE AdventureWorks;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
                       @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
  @SQLString,
  @ParmDefinition,
  @CustomerID = @IntVariable,
  @SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

Vedere anche

Riferimento

EXECUTE (Transact-SQL)
Stored procedure di sistema (Transact-SQL)

Altre risorse

Batch
Generazione di istruzioni in fase di esecuzione

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
5 dicembre 2005	Nuovo contenuto Aggiunta dell'esempio C. Contenuto modificato Correzione della sintassi del parametro OUTPUT. Nella definizione di stmt e parameter_name, sostituzione del testo "una variabile che è possibile convertire in modo implicito nel tipo di dati ntext" con il testo "una variabile Unicode".