sp_executesql (T-SQL)

Artikel
05/09/2024

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Titik akhir analitik 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.

Konvensi sintaks transact-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, yang dapat Anda unduh dari 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_namedata_type [ ,... n ]'

Satu 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 parameter @stmt tidak dikompilasi sp_executesqlhingga 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_titleoutput .

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;

Share via

sp_executesql (T-SQL)

Sintaks

Argumen

[ @stmt = ] N'statement'

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

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

Mengembalikan nilai kode

Tataan hasil

Keterangan

Izin

Contoh

J. 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

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Share via

sp_executesql (T-SQL)

Sintaks

Argumen

[ @stmt = ] N'statement'

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

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

Mengembalikan nilai kode

Tataan hasil

Keterangan

Izin

Contoh

J. 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

Saran dan Komentar

Sumber Daya Tambahan: