sp_executesql (T-SQL)
Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse 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.
Perhatian
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.
Sintaks
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 Transact-SQL dalam artikel ini menggunakan AdventureWorks2022
database sampel atau AdventureWorksDW2022
, yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
Argumen
[ @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_name data_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 = ] 'value1'
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.
{ OUT | OUTPUT }
Menunjukkan bahwa parameter adalah parameter output. parameter teks, ntext, dan gambar dapat digunakan sebagai OUTPUT
parameter, kecuali prosedurnya adalah prosedur runtime bahasa umum (CLR). 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.
Keterangan
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_executesql
parameter @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.
Catatan
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 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;
Parameter output juga dapat digunakan dengan sp_executesql
. Contoh berikut mengambil jabatan pekerjaan dari HumanResources.Employee
tabel dalam AdventureWorks2022
database sampel, dan mengembalikannya dalam parameter @max_title
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;
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.
Izin
Memerlukan keanggotaan dalam peran publik .
Contoh
J. 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.
Catatan
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 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
Menggunakan sp_executesql
dalam prosedur ini lebih efisien daripada menggunakan EXECUTE
untuk menjalankan string. Saat sp_executesql
digunakan, hanya ada 12 versi INSERT
string yang dihasilkan, satu untuk setiap tabel bulanan. Dengan EXECUTE
, setiap INSERT
string unik karena nilai parameternya berbeda. Meskipun kedua metode menghasilkan jumlah batch yang sama, kesamaan INSERT
string yang dihasilkan dengan sp_executesql
membuatnya lebih mungkin bahwa pengoptimal kueri menggunakan kembali rencana eksekusi.
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 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;
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;