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.

Konvensi sintaks transact-SQL

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