sp_executesql (Transact-SQL)

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìAzure Synapse Analytics sìParallel Data Warehouse

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.

Importante

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

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

Argomenti

[ @ stmt = ] statement
Stringa Unicode che contiene Transact-SQL un'istruzione o un batch. @stmt deve essere una costante Unicode 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, deve essere preceduta da N. Ad esempio, la costante Unicode N'sp_who' è valida, ma la costante carattere 'sp_who' non lo è. Le dimensioni massime della stringa dipendono dalla memoria disponibile nel server di database. Nei server a 64 bit la dimensione della stringa è limitata a 2 GB, ovvero la dimensione massima 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'

Ogni parametro incluso in stmt deve avere una voce corrispondente sia nell'elenco di definizioni dei parametri che nell'elenco @ @ dei valori dei parametri.

[ @ params= ] N' @ parameter_name data_type [ ,... n ] '
Stringa contenente le definizioni di tutti i parametri incorporati in @ stmt. La stringa deve essere una costante Unicode o una variabile Unicode. Ogni definizione di parametro è costituita da un nome del 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 Transact-SQL l'istruzione o il batch in @ stmt non contiene parametri, @ i parametri non sono obbligatori. 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. Deve essere presente un valore di parametro specificato per ogni parametro incluso in @ stmt. I valori non sono necessari quando Transact-SQL l'istruzione o il batch in @ stmt non ha parametri.

[ OUT | OUTPUT ]
Indica che si tratta di un parametro di output. I parametri text , ntext e image possono essere usati come parametri OUTPUT, a meno che la procedura non sia una procedura CLR (Common Language Runtime). 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 del codice restituito

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.

Commenti

sp_executesql parametri devono essere immessi nell'ordine specifico, come descritto nella sezione "Sintassi" più indietro in questo argomento. Se i parametri non vengono immessi in ordine, verrà visualizzato un messaggio di errore.

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 nel sp_executesql stmt non viene compilato fino a quando non Transact-SQL @ viene sp_executesql'istruzione . Il contenuto di stmt viene quindi compilato ed eseguito come piano di esecuzione separato 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 AdventureWorks2012.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 AdventureWorks2012.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 AdventureWorks2012.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

R. 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 AdventureWorks2012.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 per archiviare il set di OUTPUT risultati generato SELECT dall'istruzione nel @SQLString parametro . Vengono SELECT quindi eseguite due istruzioni che usano il valore del parametro OUTPUT .

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

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

D. Esecuzione di un'istruzione SELECT semplice

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

-- Uses AdventureWorks  
  
EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee   
          WHERE EmployeeKey = @level',  
          N'@level TINYINT',  
          @level = 109;  

Vedere anche

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