Parameter dengan Nilai Tabel

Parameter berbentuk tabel menyediakan cara mudah untuk mengalihkan beberapa baris data dari aplikasi klien ke SQL Server tanpa memerlukan beberapa kali bolak-balik atau logika sisi server khusus untuk memproses data. Anda dapat menggunakan parameter bernilai tabel untuk merangkum baris data dalam aplikasi klien dan mengirim data ke server dalam satu perintah berparameter. Baris data masuk disimpan dalam variabel tabel yang kemudian dapat dioperasikan dengan menggunakan Transact-SQL.

Nilai kolom dalam parameter bernilai tabel dapat diakses menggunakan pernyataan select Transact-SQL standar. Parameter bernilai tabel di ketik dengan kuat dan strukturnya secara otomatis divalidasi. Ukuran parameter bernilai tabel hanya dibatasi oleh memori server.

Nota

Anda tidak dapat mengembalikan data dalam parameter bernilai tabel. Parameter bernilai tabel hanya digunakan untuk input. Kata kunci OUTPUT tidak dapat digunakan.

Untuk informasi selengkapnya tentang parameter dengan nilai tabel, lihat sumber daya berikut.

Sumber Daya	Deskripsi
Gunakan Parameter Bernilai Tabel (Mesin Database)	Menjelaskan cara membuat dan menggunakan parameter bernilai tabel.
User-Defined Jenis Tabel	Menjelaskan jenis tabel yang ditentukan pengguna yang digunakan untuk mendeklarasikan parameter bernilai tabel.

Meneruskan Beberapa Baris di Versi SQL Server Sebelumnya

Sebelum parameter bernilai tabel diperkenalkan ke SQL Server 2008, opsi untuk meneruskan beberapa baris data ke prosedur tersimpan atau perintah SQL berparameter dibatasi. Pengembang dapat memilih dari opsi berikut untuk meneruskan beberapa baris ke server:

Gunakan serangkaian parameter individual untuk mewakili nilai dalam beberapa kolom dan baris data. Jumlah data yang dapat diteruskan dengan menggunakan metode ini dibatasi oleh jumlah parameter yang diizinkan. Prosedur SQL Server dapat memiliki, paling banyak, 2100 parameter. Logika sisi server diperlukan untuk merakit nilai individual ini ke dalam variabel tabel atau tabel sementara untuk diproses.
Bundelkan beberapa nilai data ke dalam string yang dibatasi atau dokumen XML lalu teruskan nilai teks tersebut ke prosedur atau pernyataan. Ini mengharuskan prosedur atau pernyataan dalam menyertakan logika yang diperlukan untuk memvalidasi struktur data dan membatalkan pengelompokan nilai.
Buat serangkaian statement SQL individual untuk modifikasi data yang memengaruhi beberapa baris, seperti yang dibuat dengan memanggil metode Update dari SqlDataAdapter. Perubahan dapat dikirimkan ke server satu per satu atau di-batch ke dalam grup. Namun, bahkan ketika dikirimkan dalam batch yang berisi beberapa pernyataan, setiap pernyataan dijalankan secara terpisah di server.
bcp Gunakan program utilitas atau SqlBulkCopy objek untuk memuat banyak baris data ke dalam tabel. Meskipun teknik ini sangat efisien, teknik ini tidak mendukung pemrosesan sisi server kecuali data dimuat ke dalam tabel sementara atau variabel tabel.

Membuat Jenis Parameter Table-Valued

Parameter dengan nilai tabel didasarkan pada struktur tabel tipe yang kuat yang ditentukan dengan menggunakan pernyataan Transact-SQL CREATE TYPE. Anda harus membuat jenis tabel dan menentukan struktur di SQL Server sebelum Anda dapat menggunakan parameter bernilai tabel di aplikasi klien Anda. Untuk informasi selengkapnya tentang membuat jenis tabel, lihat User-Defined Tipe Tabel.

Pernyataan berikut membuat tipe tabel bernama CategoryTableType yang terdiri dari kolom CategoryID dan CategoryName:

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )

Setelah membuat jenis tabel, Anda dapat mendeklarasikan parameter bernilai tabel berdasarkan jenis tersebut. Fragmen Transact-SQL berikut menunjukkan cara mendeklarasikan parameter bernilai tabel dalam definisi prosedur tersimpan. Perhatikan bahwa kata kunci READONLY diperlukan untuk mendeklarasikan parameter bernilai tabel.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Memodifikasi Data dengan Parameter Table-Valued (Transact-SQL)

Parameter bernilai tabel dapat digunakan dalam modifikasi data berbasis set yang memengaruhi beberapa baris dengan menjalankan satu pernyataan. Misalnya, Anda dapat memilih semua baris dalam parameter bernilai tabel dan menyisipkannya ke dalam tabel database, atau Anda bisa membuat pernyataan pembaruan dengan menggabungkan parameter bernilai tabel ke tabel yang ingin Anda perbarui.

Pernyataan UPDATE Transact-SQL berikut menunjukkan cara menggunakan parameter bernilai tabel dengan menggabungkannya ke tabel Kategori. Saat Anda menggunakan parameter bernilai tabel dengan JOIN dalam klausul FROM, Anda juga harus alias parameter tersebut, seperti yang ditunjukkan di sini, di mana parameter bernilai tabel diberi alias sebagai "ec":

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;

Contoh Transact-SQL ini menunjukkan cara memilih baris dari parameter bernilai tabel untuk melakukan INSERT dalam satu operasi berbasis set.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Pembatasan Parameter Table-Valued

Ada beberapa batasan pada parameter bernilai tabel:

Anda tidak dapat meneruskan parameter bernilai tabel ke fungsi yang ditentukan pengguna CLR.
Parameter bernilai tabel hanya dapat diindeks untuk mendukung batasan KUNCI UNIK atau PRIMER. SQL Server tidak mempertahankan statistik pada parameter bernilai tabel.
Parameter bernilai tabel di dalam kode Transact-SQL bersifat baca-saja. Anda tidak dapat memperbarui nilai kolom dalam baris parameter bernilai tabel dan Anda tidak dapat menyisipkan atau menghapus baris. Untuk mengubah data yang diteruskan ke prosedur tersimpan atau pernyataan berparameter dalam parameter bernilai tabel, Anda harus menyisipkan data ke dalam tabel sementara atau ke dalam variabel tabel.
Anda tidak dapat menggunakan pernyataan ALTER TABLE untuk mengubah desain parameter bernilai tabel.

Pengaturan Contoh SqlParameter

System.Data.SqlClient mendukung pengisian parameter bernilai tabel dari DataTable, DbDataReader atau IEnumerable<T> \ SqlDataRecord objek. Anda harus menentukan nama tipe untuk parameter bernilai tabel dengan menggunakan properti TypeName dari SqlParameter. TypeName harus cocok dengan nama tipe yang sesuai yang sebelumnya dibuat pada server. Fragmen kode berikut menunjukkan cara mengonfigurasi SqlParameter untuk menyisipkan data.

Dalam contoh berikut, sebuah addedCategories variabel berisi sebuah DataTable. Untuk melihat bagaimana variabel diisi, lihat contoh di bagian berikutnya, Meneruskan Parameter Table-Valued ke Prosedur Tersimpan.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";

' Configure the command and parameter.  
Dim insertCommand As New SqlCommand(sqlInsert, connection)  
Dim tvpParam As SqlParameter = _  
   insertCommand.Parameters.AddWithValue( _  
  "@tvpNewCategories", addedCategories)  
tvpParam.SqlDbType = SqlDbType.Structured  
tvpParam.TypeName = "dbo.CategoryTableType"

Anda juga dapat menggunakan objek apa pun yang berasal dari DbDataReader untuk mengalirkan baris data ke parameter bernilai tabel, seperti yang ditunjukkan dalam fragmen ini:

// Configure the SqlCommand and table-valued parameter.  
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
insertCommand.CommandType = CommandType.StoredProcedure;  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);  
tvpParam.SqlDbType = SqlDbType.Structured;

' Configure the SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  dataReader)  
tvpParam.SqlDbType = SqlDbType.Structured

Meneruskan Parameter Table-Valued ke Prosedur Tersimpan

Contoh ini menunjukkan cara meneruskan data parameter bernilai tabel ke prosedur tersimpan. Kode mengekstrak baris yang ditambahkan ke dalam DataTable baru dengan menggunakan metode GetChanges. Kode kemudian mendefinisikan SqlCommand, mengatur CommandType properti ke StoredProcedure. SqlParameter diisi dengan metode AddWithValue dan SqlDbType diatur ke Structured. SqlCommand kemudian dijalankan dengan metode ExecuteNonQuery.

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}

' Assumes connection is an open SqlConnection object.  
Using connection  
   '  Create a DataTable with the modified rows.  
   Dim addedCategories As DataTable = _  
     CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Configure the SqlCommand and SqlParameter.  
   Dim insertCommand As New SqlCommand( _  
     "usp_InsertCategories", connection)  
   insertCommand.CommandType = CommandType.StoredProcedure  
   Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
     "@tvpNewCategories", addedCategories)  
   tvpParam.SqlDbType = SqlDbType.Structured  
  
   '  Execute the command.  
   insertCommand.ExecuteNonQuery()  
End Using

Meneruskan Parameter Table-Valued ke Pernyataan SQL Berparameter

Contoh berikut menunjukkan cara menyisipkan data ke dalam dbo. Tabel kategori dengan menggunakan pernyataan INSERT dengan subkueri SELECT yang memiliki parameter bernilai tabel sebagai sumber data. Saat meneruskan parameter bernilai tabel ke pernyataan SQL berparameter, Anda harus menentukan nama jenis untuk parameter bernilai tabel dengan menggunakan properti baru TypeName dari SqlParameter. Ini TypeName harus cocok dengan nama tipe yang kompatibel yang sebelumnya dibuat di server. Kode dalam contoh ini menggunakan TypeName properti untuk mereferensikan struktur jenis yang ditentukan dalam dbo. CategoryTableType.

Nota

Jika Anda memberikan nilai untuk kolom identitas dalam parameter bernilai tabel, Anda harus mengeluarkan pernyataan set IDENTITY_INSERT untuk sesi tersebut.

// Assumes connection is an open SqlConnection.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Define the INSERT-SELECT statement.  
  string sqlInsert =
      "INSERT INTO dbo.Categories (CategoryID, CategoryName)"  
      + " SELECT nc.CategoryID, nc.CategoryName"  
      + " FROM @tvpNewCategories AS nc;"  

  // Configure the command and parameter.  
  SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  tvpParam.TypeName = "dbo.CategoryTableType";  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}

' Assumes connection is an open SqlConnection.  
Using connection  
  ' Create a DataTable with the modified rows.  
  Dim addedCategories As DataTable = _  
    CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Define the INSERT-SELECT statement.  
  Dim sqlInsert As String = _  
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _  
  & " SELECT nc.CategoryID, nc.CategoryName" _  
  & " FROM @tvpNewCategories AS nc;"  
  
  ' Configure the command and parameter.  
  Dim insertCommand As New SqlCommand(sqlInsert, connection)  
  Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
    "@tvpNewCategories", addedCategories)  
  tvpParam.SqlDbType = SqlDbType.Structured  
  tvpParam.TypeName = "dbo.CategoryTableType"  
  
  ' Execute the query  
  insertCommand.ExecuteNonQuery()  
End Using

Mengalirkan Baris dengan DataReader

Anda juga dapat menggunakan objek apa pun yang berasal dari DbDataReader untuk mengalirkan baris data ke parameter bernilai tabel. Fragmen kode berikut menunjukkan pengambilan data dari database Oracle dengan menggunakan OracleCommand dan OracleDataReader. Kode kemudian mengonfigurasi SqlCommand untuk memanggil prosedur tersimpan dengan satu parameter input. Properti SqlDbType dari SqlParameter diatur ke Structured. AddWithValue meneruskan kumpulan hasil OracleDataReader ke prosedur tersimpan sebagai parameter bernilai tabel.

// Assumes connection is an open SqlConnection.  
// Retrieve data from Oracle.  
OracleCommand selectCommand = new OracleCommand(  
   "Select CategoryID, CategoryName FROM Categories;",  
   oracleConnection);  
OracleDataReader oracleReader = selectCommand.ExecuteReader(  
   CommandBehavior.CloseConnection);  
  
 // Configure the SqlCommand and table-valued parameter.  
 SqlCommand insertCommand = new SqlCommand(  
   "usp_InsertCategories", connection);  
 insertCommand.CommandType = CommandType.StoredProcedure;  
 SqlParameter tvpParam =  
    insertCommand.Parameters.AddWithValue(  
    "@tvpNewCategories", oracleReader);  
 tvpParam.SqlDbType = SqlDbType.Structured;  
  
 // Execute the command.  
 insertCommand.ExecuteNonQuery();

' Assumes connection is an open SqlConnection.  
' Retrieve data from Oracle.  
Dim selectCommand As New OracleCommand( _  
  "Select CategoryID, CategoryName FROM Categories;", _  
  oracleConnection)  
Dim oracleReader As OracleDataReader = _  
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)  
  
' Configure SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  oracleReader)  
tvpParam.SqlDbType = SqlDbType.Structured  
  
' Execute the command.  
insertCommand.ExecuteNonQuery()

Lihat juga

Last updated on 2025-06-17

Bagikan melalui