sp_executesql (Transact-SQL)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Sistem Platform Analitik (PDW)Titik akhir analitik SQL di Microsoft Fabric Gudang di Microsoft Fabric Database SQL di Microsoft Fabric

Menjalankan pernyataan Transact-SQL atau batch yang dapat digunakan kembali berkali-kali, atau yang dibangun secara dinamis. Pernyataan atau batch Transact-SQL dapat berisi parameter yang disematkan.

Caution

Pernyataan Transact-SQL yang dikompilasi runtime dapat mengekspos aplikasi ke serangan berbahaya. Anda harus membuat parameter kueri Saat menggunakan sp_executesql. Untuk informasi selengkapnya, lihat Injeksi SQL.

Konvensi sintaks transact-SQL

Syntax

Sintaks untuk SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, dan Analytics Platform System (PDW).

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

Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2025 atau AdventureWorksDW2025, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.

Arguments

Important

Argumen untuk prosedur tersimpan yang diperluas harus dimasukkan dalam urutan tertentu seperti yang dijelaskan di bagian Sintaks. Jika parameter dimasukkan di luar urutan, pesan kesalahan terjadi.

[ @stmt = ] N'statement'

String Unicode yang berisi pernyataan atau batch Transact-SQL. @stmt harus berupa konstanta Unicode atau variabel Unicode. Ekspresi Unicode yang lebih kompleks, seperti menggabungkan dua string dengan + operator, tidak diizinkan. Konstanta karakter tidak diperbolehkan. Konstanta Unicode harus diawali dengan N. Misalnya, konstanta N'sp_who' Unicode valid, tetapi konstanta 'sp_who' karakter tidak. Ukuran string hanya dibatasi oleh memori server database yang tersedia. Pada server 64-bit, ukuran string dibatasi hingga 2 GB, ukuran maksimum nvarchar(maks).

@stmt dapat berisi parameter yang memiliki formulir yang sama dengan nama variabel. Contohnya:

N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter';

Setiap parameter yang disertakan dalam @stmt harus memiliki entri yang sesuai dalam daftar definisi parameter @params dan daftar nilai parameter.

[ @params = ] N'@parameter_namedata_type [ , ... n ]'

String yang berisi definisi semua parameter yang disematkan dalam @stmt. String harus berupa konstanta Unicode atau variabel Unicode. Setiap definisi parameter terdiri dari nama parameter dan jenis data. n adalah tempat penampung yang menunjukkan lebih banyak definisi parameter. Setiap parameter yang ditentukan dalam @stmt harus ditentukan dalam @params. Jika pernyataan Transact-SQL atau batch di @stmt tidak berisi parameter, @params tidak diperlukan. Nilai default untuk parameter ini adalah NULL.

[ @param1 = ] 'nilai1'

Nilai untuk parameter pertama yang ditentukan dalam string parameter. Nilainya bisa berupa konstanta Unicode atau variabel Unicode. Harus ada nilai parameter yang disediakan untuk setiap parameter yang disertakan dalam @stmt. Nilai tidak diperlukan saat pernyataan Transact-SQL atau batch dalam @stmt tidak memiliki parameter.

{ KELUAR | KELUARAN }

Menunjukkan bahwa parameter adalah parameter output. parameter teks, ntext). Parameter output yang menggunakan OUTPUT kata kunci dapat menjadi tempat penampung kursor, kecuali prosedurnya adalah prosedur CLR.

[ ... n ]

Tempat penampung untuk nilai parameter tambahan. Nilai hanya dapat berupa konstanta atau variabel. Nilai tidak boleh berupa ekspresi yang lebih kompleks seperti fungsi, atau ekspresi yang dibangun dengan menggunakan operator.

Mengembalikan nilai kode

0 (berhasil) atau bukan nol (kegagalan).

Tataan hasil

Mengembalikan kumpulan hasil dari semua pernyataan SQL yang disertakan dalam string SQL.

Remarks

sp_executesql parameter harus dimasukkan dalam urutan tertentu seperti yang dijelaskan di bagian Sintaks sebelumnya dalam artikel ini. Jika parameter dimasukkan di luar urutan, pesan kesalahan terjadi.

sp_executesql memiliki perilaku EXECUTE yang sama mengenai batch, cakupan nama, dan konteks database. Pernyataan Transact-SQL atau batch dalam sp_executesqlparameter @stmt tidak dikompilasi hingga sp_executesql pernyataan dijalankan. Konten @stmt kemudian dikompilasi dan dijalankan sebagai rencana eksekusi terpisah dari rencana eksekusi batch yang disebut sp_executesql. Batch sp_executesql tidak dapat mereferensikan variabel yang dideklarasikan dalam batch yang memanggil sp_executesql. Kursor atau variabel lokal dalam sp_executesql batch tidak terlihat oleh batch yang memanggil sp_executesql. Perubahan dalam konteks database hanya berlangsung hingga akhir pernyataan sp_executesql.

sp_executesql dapat digunakan alih-alih prosedur tersimpan untuk menjalankan pernyataan Transact-SQL berkali-kali ketika perubahan nilai parameter ke pernyataan adalah satu-satunya variasi. Karena pernyataan Transact-SQL itu sendiri tetap konstan dan hanya nilai parameter yang berubah, pengoptimal kueri SQL Server kemungkinan akan menggunakan kembali rencana eksekusi yang dihasilkannya untuk eksekusi pertama. Dalam skenario ini, performa setara dengan prosedur tersimpan.

Note

Untuk meningkatkan performa, gunakan nama objek yang sepenuhnya memenuhi syarat dalam string pernyataan.

sp_executesql mendukung pengaturan nilai parameter secara terpisah dari string Transact-SQL, seperti yang ditunjukkan dalam contoh berikut.

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;

Parameter output juga dapat digunakan dengan sp_executesql. Contoh berikut mengambil jabatan pekerjaan dari HumanResources.Employee tabel dalam AdventureWorks2025 database sampel, dan mengembalikannya dalam parameter @max_titleoutput .

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;

Mampu mengganti parameter dalam sp_executesql menawarkan keuntungan berikut daripada menggunakan EXECUTE pernyataan untuk menjalankan string:

Karena teks aktual pernyataan Transact-SQL dalam sp_executesql string tidak berubah di antara eksekusi, pengoptimal kueri mungkin cocok dengan pernyataan Transact-SQL dalam eksekusi kedua dengan rencana eksekusi yang dihasilkan untuk eksekusi pertama. Oleh karena itu, SQL Server tidak perlu mengkompilasi pernyataan kedua.
String Transact-SQL dibuat hanya sekali.
Parameter bilangan bulat ditentukan dalam format aslinya. Transmisi ke Unicode tidak diperlukan.

OPTIMIZED_SP_EXECUTESQL

Berlaku untuk: SQL Server 2025 (17.x), Azure SQL Database, dan database SQL di Microsoft Fabric

Ketika konfigurasi cakupan database OPTIMIZED_SP_EXECUTESQL diaktifkan, perilaku kompilasi batch yang dikirimkan menggunakan sp_executesql menjadi identik dengan perilaku kompilasi berseri yang digunakan objek seperti prosedur tersimpan dan pemicu yang saat ini digunakan.

Ketika batch identik (tidak termasuk perbedaan parameter apa pun), OPTIMIZED_SP_EXECUTESQL opsi mencoba untuk mendapatkan kunci kompilasi sebagai mekanisme penegakan untuk menjamin bahwa proses kompilasi diserialisasikan. Kunci ini memastikan bahwa jika beberapa sesi memanggil sp_executesql secara bersamaan, sesi tersebut akan menunggu sambil mencoba mendapatkan kunci kompilasi eksklusif setelah sesi pertama memulai proses kompilasi. Eksekusi pertama kompilasi sp_executesql dan menyisipkan rencana yang dikompilasi ke dalam cache paket. Sesi lain membatalkan menunggu pada kunci kompilasi dan menggunakan kembali rencana setelah tersedia.

OPTIMIZED_SP_EXECUTESQL Tanpa opsi , beberapa pemanggilan batch identik yang dijalankan melalui sp_executesql kompilasi secara paralel dan menempatkan salinan mereka sendiri dari rencana yang dikompilasi ke dalam cache rencana, yang menggantikan atau menduplikasi entri cache rencana dalam beberapa kasus.

Note

Sebelum Anda mengaktifkan OPTIMIZED_SP_EXECUTESQL konfigurasi tercakup database, jika statistik pembaruan otomatis diaktifkan, Anda juga harus mengaktifkan opsi asinkron statistik pembaruan otomatis dengan opsi konfigurasi cakupan database ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY . Mengaktifkan kedua opsi ini dapat secara signifikan mengurangi probabilitas bahwa masalah performa yang terkait dengan waktu kompilasi yang lama bersama dengan kunci eksklusif manajer kunci yang berlebihan (LCK_M_X) dan WAIT_ON_SYNC_STATISTICS_REFRESH menunggu.

OPTIMIZED_SP_EXECUTESQL nonaktif secara default. Untuk mengaktifkan OPTIMIZED_SP_EXECUTESQL di tingkat database, gunakan pernyataan Transact-SQL berikut:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;

Permissions

Memerlukan keanggotaan dalam peran publik .

Examples

A. Menjalankan pernyataan SELECT

Contoh berikut membuat dan menjalankan SELECT pernyataan yang berisi parameter tersemat bernama @level.

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

B. Menjalankan string yang dibangun secara dinamis

Contoh berikut menunjukkan penggunaan sp_executesql untuk menjalankan string yang dibangun secara dinamis. Contoh prosedur tersimpan digunakan untuk menyisipkan data ke dalam sekumpulan tabel yang digunakan untuk mempartisi data penjualan selama setahun. Ada satu tabel untuk setiap bulan dalam setahun yang memiliki format berikut:

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

Sampel prosedur tersimpan ini secara dinamis membangun dan menjalankan INSERT pernyataan untuk menyisipkan pesanan baru ke dalam tabel yang benar. Contoh menggunakan tanggal pesanan untuk membuat nama tabel yang harus berisi data, lalu menggabungkan nama tersebut ke dalam INSERT pernyataan.

Note

Ini adalah contoh dasar untuk sp_executesql. Contoh tidak berisi pemeriksaan kesalahan, dan tidak menyertakan pemeriksaan untuk aturan bisnis, seperti menjamin bahwa nomor pesanan tidak diduplikasi di antara tabel.

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);
EXECUTE sp_executesql
    @InsertString, N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME, @InsOrdMonth INT, @InsDelDate DATETIME',
    @PrmOrderID,
    @PrmCustomerID,
    @PrmOrderDate,
    @OrderMonth,
    @PrmDeliveryDate;
GO

Menggunakan sp_executesql dalam prosedur ini lebih efisien daripada menggunakan EXECUTE untuk menjalankan string yang dibangun secara dinamis, karena memungkinkan penggunaan penanda parameter. Penanda parameter membuatnya lebih mungkin bahwa Mesin Database menggunakan kembali rencana kueri yang dihasilkan, yang membantu menghindari kompilasi kueri tambahan. Dengan EXECUTE, setiap INSERT string unik karena nilai parameter berbeda, dan akan ditambahkan ke akhir string yang dihasilkan secara dinamis. Saat dijalankan, kueri tidak akan diparameterkan dengan cara yang mendorong penggunaan kembali rencana, dan harus dikompilasi sebelum setiap INSERT pernyataan dijalankan, yang akan menambahkan entri kueri yang di-cache terpisah dalam cache paket.

C. Menggunakan parameter OUTPUT

Contoh berikut menggunakan OUTPUT parameter untuk menyimpan kumpulan hasil yang dihasilkan oleh SELECT pernyataan dalam @SQLString parameter . Dua SELECT pernyataan kemudian dijalankan yang menggunakan nilai OUTPUT parameter .

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;

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

D. Menjalankan pernyataan SELECT

Contoh berikut membuat dan menjalankan SELECT pernyataan yang berisi parameter tersemat bernama @level.

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

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-18

Bagikan melalui

sp_executesql (Transact-SQL)

Syntax

Arguments

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_namedata_type [ , ... n ]'

[ @param1 = ] 'nilai1'

{ KELUAR | KELUARAN }

[ ... n ]

Mengembalikan nilai kode

Tataan hasil

Remarks

OPTIMIZED_SP_EXECUTESQL

Permissions

Examples

A. Menjalankan pernyataan SELECT

B. Menjalankan string yang dibangun secara dinamis

C. Menggunakan parameter OUTPUT

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

D. Menjalankan pernyataan SELECT

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: