sp_executesql (Transact-SQL)

Articolo
11/02/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW) Endpoint di analisi SQL in Microsoft Fabric Warehouse in Microsoft Fabric

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

Attenzione

Le istruzioni Transact-SQL compilate in fase di esecuzione possono esporre applicazioni ad attacchi dannosi. È consigliabile parametrizzare le query quando si usa sp_executesql. Per altre informazioni, vedere Attacco intrusivo nel codice SQL.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di Azure, Istanza gestita di SQL di Azure, Azure Synapse Analytics e piattaforma di analisi (PDW).

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

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

Argomenti

[ @stmt = ] N'statement'

Una stringa Unicode che contiene un'istruzione Transact-SQL 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 carattere non sono consentite. Le costanti Unicode devono essere precedute da un prefisso 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, le dimensioni della stringa sono limitate a 2 GB, la dimensione massima di nvarchar(max).

@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 delle definizioni dei parametri @params che nell'elenco dei valori dei parametri.

[ @params = ] N'@parameter_name data_type [ , ...n ]'

Stringa che contiene 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 più definizioni di parametro. Ogni parametro specificato in @stmt 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'

Un valore per il primo parametro definito nella stringa di parametri. Il valore può essere una costante o una variabile Unicode. È necessario specificare un valore di parametro per ogni parametro incluso in @stmt. I valori non sono necessari quando l'istruzione Transact-SQL o il batch in @stmt non dispone di parametri.

{ OUT | OUTPUT }

Indica che si tratta di un parametro di output. I parametri text, ntext e image possono essere usati come OUTPUT parametri, a meno che la routine non sia una routine CLR (Common Language Runtime). Un parametro di output che usa la OUTPUT parola chiave può essere un segnaposto del cursore, a meno che la routine non sia una routine CLR.

[ ... n ]

Un segnaposto per i valori di parametri extra. 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.

Osservazioni:

sp_executesql I parametri devono essere immessi nell'ordine specifico, come descritto nella sezione Sintassi precedente in questo articolo. Se i parametri non vengono immessi in ordine, viene visualizzato un messaggio di errore.

sp_executesql ha lo stesso comportamento dei EXECUTE batch, dell'ambito dei nomi e del contesto del database. L'istruzione Transact-SQL o il sp_executesql batch nel parametro @stmt non viene compilato fino a quando l'istruzione sp_executesql non viene eseguita. 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 sp_executesql batch non può fare riferimento a variabili dichiarate nel batch che chiama sp_executesql. I cursori locali o le variabili nel sp_executesql batch non sono visibili al batch che chiama sp_executesql. Le modifiche apportate al contesto del database durano solo fino al termine dell'esecuzione dell'istruzione sp_executesql .

sp_executesql può essere usato invece di stored procedure per eseguire un'istruzione Transact-SQL molte volte quando la modifica dei valori dei parametri nell'istruzione è l'unica variante. Poiché l'istruzione Transact-SQL stessa rimane costante e vengono modificati solo i valori dei parametri, è probabile che Query Optimizer di SQL Server riutilizzi il piano di esecuzione generato per la prima esecuzione. In questo scenario le prestazioni sono equivalenti a quella di una stored procedure.

Nota

Per migliorare le prestazioni, usare nomi di oggetto completi nella stringa dell'istruzione.

sp_executesql supporta l'impostazione dei valori dei parametri separatamente dalla stringa Transact-SQL, come illustrato nell'esempio seguente.

DECLARE @IntVariable AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);

/* Build the SQL string once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.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;

I parametri di output possono essere usati anche con sp_executesql. Nell'esempio seguente viene recuperato un titolo di processo dalla HumanResources.Employee tabella del AdventureWorks2022 database di esempio e viene restituito nel parametro @max_titledi output .

DECLARE @IntVariable AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS VARCHAR (30);

SET @IntVariable = 197;

SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
   FROM AdventureWorks2022.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'uso dell'istruzione EXECUTE per eseguire una stringa:

Poiché il testo effettivo dell'istruzione Transact-SQL nella sp_executesql stringa non cambia tra le esecuzioni, Query Optimizer corrisponde probabilmente all'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. Il cast in Unicode non è obbligatorio.

OPTIMIZED_SP_EXECUTESQL

Si applica a: Database SQL di Azure

Quando la configurazione con ambito database di OPTIMIZED_SP_EXECUTESQL è abilitata, il comportamento di compilazione dei batch inviati tramite sp_executesql diventa identico al comportamento di compilazione serializzato usato da oggetti quali stored procedure e trigger.

Quando i batch sono identici (esclusi eventuali differenze di parametro), l'opzione OPTIMIZED_SP_EXECUTESQL tenta di ottenere un blocco di compilazione come meccanismo di imposizione per garantire che il processo di compilazione venga serializzato. Questo blocco garantisce che, se più sessioni richiamano sp_executesql contemporaneamente, tali sessioni attenderanno durante il tentativo di ottenere un blocco di compilazione esclusivo dopo che la prima sessione avvia il processo di compilazione. La prima esecuzione di sp_executesql compila e inserisce il piano compilato nella cache dei piani. Altre sessioni interrompino l'attesa del blocco di compilazione e riutilizzano il piano una volta che diventa disponibile.

Senza l'opzione OPTIMIZED_SP_EXECUTESQL , più chiamate di batch identici eseguiti tramite sp_executesql compilazione in parallelo e posizionare le proprie copie di un piano compilato nella cache dei piani, che sostituiscono o duplicano le voci della cache dei piani in alcuni casi.

Nota

Prima di abilitare la OPTIMIZED_SP_EXECUTESQL configurazione con ambito database, se le statistiche di aggiornamento automatico sono abilitate, è necessario abilitare anche l'opzione asincrona per l'aggiornamento automatico delle statistiche con l'opzione di configurazione con ambito database ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY . L'abilitazione di queste due opzioni può ridurre significativamente la probabilità che i problemi di prestazioni correlati a lunghi tempi di compilazione insieme a blocchi eccessivi e blocchi esclusivi (LCK_M_X) e WAIT_ON_SYNC_STATISTICS_REFRESH attese.

OPTIMIZED_SP_EXECUTESQL è disattivato per impostazione predefinita. Per abilitare OPTIMIZED_SP_EXECUTESQL a livello di database, usare l'istruzione Transact-SQL seguente:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Autorizzazioni

È richiesta l'appartenenza al ruolo public .

Esempi

R. Eseguire un'istruzione SELECT

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

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. Eseguire una stringa compilata dinamicamente

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. Esiste una tabella per ogni mese dell'anno con il 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

Questo è un esempio di base per sp_executesql. L'esempio non contiene il controllo degli errori e non include controlli per le regole business, ad esempio la garanzia che i numeri di ordine non siano duplicati tra le tabelle.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS 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'uso sp_executesql di in questa procedura è più efficiente rispetto all'uso EXECUTE di per eseguire la stringa compilata dinamicamente, perché consente l'uso di marcatori di parametro. Gli indicatori di parametro rendono più probabile che il motore di database riutilizza il piano di query generato, che consente di evitare compilazioni di query aggiuntive. Con EXECUTE, ogni INSERT stringa è univoca perché i valori dei parametri sono diversi e verrebbero aggiunti alla fine della stringa generata dinamicamente. Quando viene eseguita, la query non verrebbe parametrizzata in modo da incoraggiare il riutilizzo del piano e dovrà essere compilata prima dell'esecuzione di ogni INSERT istruzione, che aggiungerebbe una voce separata memorizzata nella cache dei piani.

C. Usare il parametro OUTPUT

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

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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. Eseguire un'istruzione SELECT

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

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

Condividi tramite

sp_executesql (Transact-SQL)

Sintassi

Argomenti

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ , ...n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

Valori del codice restituito

Set di risultati

Osservazioni:

OPTIMIZED_SP_EXECUTESQL

Autorizzazioni

Esempi

R. Eseguire un'istruzione SELECT

B. Eseguire una stringa compilata dinamicamente

C. Usare il parametro OUTPUT

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

D. Eseguire un'istruzione SELECT

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

sp_executesql (Transact-SQL)

Sintassi

Argomenti

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ , ...n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

Valori del codice restituito

Set di risultati

Osservazioni:

OPTIMIZED_SP_EXECUTESQL

Autorizzazioni

Esempi

R. Eseguire un'istruzione SELECT

B. Eseguire una stringa compilata dinamicamente

C. Usare il parametro OUTPUT

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

D. Eseguire un'istruzione SELECT

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive