sp_executesql (Transact-SQL)

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

Nota sulla sicurezza
Istruzioni Transact-SQL compilate in fase di esecuzione possono esporre le applicazioni ad attacchi dannosi, ad esempio intrusioni nel codice SQL.

Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

• [ @statement = ] statement
  Stringa Unicode contenente un'istruzione o un batch Transact-SQL. statement deve essere una costante o una variabile Unicode. Non sono consentite le espressioni Unicode più complesse, ad esempio per la concatenazione di due stringhe tramite l'operatore +. Le costanti di tipo carattere non sono consentite. Se viene specificata una costante Unicode, è necessario anteporvi un prefisso N. La costante Unicode N'sp_who' ad esempi è valida, mentre la costante relativa al carattere 'sp_who' non lo è. Le dimensioni massime della stringa dipendono dalla memoria disponibile nel server 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 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 statement deve essere definito in @params. Se l'istruzione Transact-SQL o il batch 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 compilate tramite operatori.

Valori restituiti

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

Set di risultati

Restituisce i set di risultati di tutte le istruzioni SQL compilate 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 Transact-SQL o il batch nel parametro sp_executesql stmt 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.

L'impostazione dei valori dei parametri è supportata in modo autonomo da sp_executesql 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 BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

È inoltre possibile utilizzare parametri di output con sp_executesql. Nell'esempio seguente un titolo professionale viene recuperato dalla tabella AdventureWorks2008R2.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(JobTitle) 
   FROM AdventureWorks2008R2.HumanResources.Employee
   WHERE BusinessEntityID = @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 compilata 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.

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 AdventureWorks2008R2.HumanResources.Employee 
          WHERE BusinessEntityID = @level',
          N'@level tinyint',
          @level = 109;

B. Esecuzione di una stringa compilata in modo dinamico

Nell'esempio seguente viene illustrato l'utilizzo di sp_executesql per l'esecuzione di una stringa compilata 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 compila in modo dinamico ed esegue un'istruzione INSERT per l'inserimento di nuovi ordini nella tabella corretta. La data dell'ordine viene utilizzata per compilare 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 AdventureWorks2008R2;
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;

Per ulteriori esempi, vedere Utilizzo di sp_executesql.

Vedere anche

Riferimento

EXECUTE (Transact-SQL)

Stored procedure di sistema (Transact-SQL)

Concetti

Attacco intrusivo nel codice SQL

Batch

Generazione di istruzioni in fase di esecuzione