sp_executesql (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 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 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 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.

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

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.

Esempi

A. Esecuzione di un'istruzione SELECT semplice

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

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)
    )

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

C. Utilizzo 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

Concetti