sp_executesql (T-SQL)
Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric
Menjalankan pernyataan Transact-SQL atau batch yang dapat digunakan kembali berkali-kali, atau yang telah dibangun secara dinamis. Pernyataan atau batch Transact-SQL dapat berisi parameter yang disematkan.
Penting
Menjalankan pernyataan Transact-SQL yang dikompilasi waktu dapat mengekspos aplikasi ke serangan berbahaya.
Sintaks
-- 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 ] }
]
Argumen
[ @stmt = ] Pernyataan
Adalah 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. Jika konstanta Unicode ditentukan, konstanta tersebut harus diawali dengan N. Misalnya, konstanta Unicode N'sp_who' valid, tetapi konstanta karakter 'sp_who' 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).
Catatan
@stmt dapat berisi parameter yang memiliki formulir yang sama dengan nama variabel, misalnya: 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 ] '
Adalah salah satu string yang berisi definisi semua parameter yang telah 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 definisi parameter tambahan. 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'
Adalah 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 ketika 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 parameter OUTPUT, kecuali prosedurnya adalah prosedur runtime bahasa umum (CLR). Parameter output yang menggunakan kata kunci OUTPUT dapat menjadi tempat penampung kursor, kecuali prosedurnya adalah prosedur CLR.
n
Adalah 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 topik ini. Jika parameter dimasukkan di luar urutan, pesan kesalahan akan terjadi.
sp_executesql memiliki perilaku yang sama dengan EXECUTE sehubungan dengan batch, cakupan nama, dan konteks database. Pernyataan Transact-SQL atau batch dalam parameter sp_executesql @stmt tidak dikompilasi hingga pernyataan sp_executesql 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 batch sp_executesql 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.
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 one time.*/
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 di sp_executesql menawarkan keuntungan berikut untuk menggunakan pernyataan EXECUTE untuk menjalankan string:
Karena teks aktual pernyataan Transact-SQL dalam string sp_executesql tidak berubah di antara eksekusi, pengoptimal kueri mungkin akan 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 hanya dibangun satu kali.
Parameter bilangan bulat ditentukan dalam format aslinya. Transmisi ke Unicode tidak diperlukan.
Izin
Memerlukan keanggotaan dalam peran publik.
Contoh
J. Menjalankan pernyataan SELECT sederhana
Contoh berikut membuat dan menjalankan pernyataan sederhana SELECT
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 sederhana 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 string INSERT yang dihasilkan, satu untuk setiap tabel bulanan. Dengan EXECUTE, setiap string INSERT unik karena nilai parameternya berbeda. Meskipun kedua metode menghasilkan jumlah batch yang sama, kesamaan string INSERT yang dihasilkan oleh sp_executesql membuatnya lebih mungkin bahwa pengoptimal kueri akan 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 sederhana
Contoh berikut membuat dan menjalankan pernyataan sederhana SELECT
yang berisi parameter tersemat bernama @level
.
-- Uses AdventureWorks2022
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;
Lihat Juga
EXECUTE (Transact-SQL)
Prosedur Tersimpan Sistem (Transact-SQL)
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk