sp_executesql (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)Endpoint di analisi SQL in Microsoft FabricWarehouse 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.

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 usano il AdventureWorks2022 database di esempio, che è possibile scaricare dalla home page degli esempi di Microsoft SQL Server e dei progetti della community.

Argomenti

[ @stmt = ] N'statement'

Stringa Unicode che contiene un'istruzione Transact-SQL o un batch. @stmt deve essere una costante Unicode o una variabile Unicode. Le espressioni Unicode più complesse, ad esempio la concatenazione di due stringhe con l'operatore + , non sono consentite. Le costanti carattere non sono consentite. Le costanti Unicode devono essere precedute da un prefisso N. Ad esempio, la costante N'sp_who' Unicode è valida, ma la costante 'sp_who' carattere non è. 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_namedata_type [ ,... n ]'

Una 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 parametri. Ogni parametro specificato in @stmt 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 del parametro. 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 ha 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 ]

Segnaposto per i valori dei parametri aggiuntivi. I valori possono essere solo costanti o variabili. I valori non possono essere espressioni più complesse, ad esempio funzioni o espressioni compilate tramite operatori.

Valori del codice restituito

0 (esito positivo) o diverso da zero (errore).

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 vengono immessi in ordine non corretto, si verifica 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_executesqlbatch nel parametro @stmt non vengono compilati finché 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.

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

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition 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 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 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.

Autorizzazioni

È richiesta l'appartenenza al ruolo public .

Esempi

R. Eseguire un'istruzione edizione Standard LECT

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.

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'uso sp_executesql di in questa procedura è più efficiente rispetto all'uso EXECUTE di per eseguire una stringa. Quando sp_executesql viene usato, sono presenti solo 12 versioni della INSERT stringa generate, una per ogni tabella mensile. Con EXECUTE, ogni INSERT stringa è univoca perché i valori dei parametri sono diversi. Anche se entrambi i metodi generano lo stesso numero di batch, la somiglianza delle INSERT stringhe generate da sp_executesql rende più probabile che Query Optimizer riutilizzi i piani di esecuzione.

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 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. Eseguire un'istruzione edizione Standard LECT

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;

Contenuto correlato

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