sp_describe_undeclared_parameters (T-SQL)

Artikel
08/21/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed Instance Azure Synapse Analytics titik akhir analitik SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Mengembalikan tataan hasil yang berisi metadata tentang parameter yang tidak dinyatakan dalam batch Transact-SQL. Mempertimbangkan setiap parameter yang digunakan dalam batch @tsql , tetapi tidak dideklarasikan dalam @params. Kumpulan hasil dikembalikan yang berisi satu baris untuk setiap parameter tersebut, dengan informasi jenis yang disimpulkan untuk parameter tersebut. Prosedur mengembalikan tataan hasil kosong jika batch input @tsql tidak memiliki parameter kecuali yang dinyatakan dalam @params.

Konvensi sintaks transact-SQL

Sintaksis

sp_describe_undeclared_parameters   
    [ @tsql = ] 'Transact-SQL_batch'   
    [ , [ @params = ] N'parameters' data type ] [, ...n]

Catatan

Untuk menggunakan prosedur tersimpan ini di Azure Synapse Analytics di kumpulan SQL khusus, atur tingkat kompatibilitas database ke 20 atau lebih tinggi. Untuk menolak, ubah tingkat kompatibilitas database menjadi 10.

Argumen

[ @tsql = ] 'Transact-SQL\_batch' Satu atau beberapa pernyataan Transact-SQL. Transact-SQL_batch mungkin nvarchar(n) atau nvarchar(max).

[ @params = ] @params N'parameters'' menyediakan string deklarasi untuk parameter untuk batch Transact-SQL, mirip dengan cara kerja sp_executesql. Parameter mungkin nvarchar(n) atau nvarchar(maks).

Adalah salah satu string yang berisi definisi semua parameter yang telah disematkan dalam Transact-SQL_batch. 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. Jika pernyataan Transact-SQL atau batch dalam pernyataan tidak berisi parameter, @params tidak diperlukan. Nilai default untuk parameter ini adalah NULL.

Tipe data
Tipe data parameter.

Mengembalikan Nilai Kode

sp_describe_undeclared_parameters selalu mengembalikan status pengembalian nol pada keberhasilan. Jika prosedur melemparkan kesalahan dan prosedur disebut sebagai RPC, status pengembalian diisi oleh jenis kesalahan seperti yang dijelaskan di kolom error_type sys.dm_exec_describe_first_result_set. Jika prosedur dipanggil dari Transact-SQL, nilai pengembalian selalu nol, bahkan dalam kasus kesalahan.

Tataan Hasil

sp_describe_undeclared_parameters mengembalikan tataan hasil berikut.

Nama kolom	Jenis data	Deskripsi
parameter_ordinal	int NOT NULL	Berisi posisi ordinal parameter dalam tataan hasil. Posisi parameter pertama akan ditentukan sebagai 1.
nama	sysname NOT NULL	Berisi nama parameter.
suggested_system_type_id	int NOT NULL	Berisi system_type_id jenis data parameter seperti yang ditentukan dalam sys.type. Untuk jenis CLR, meskipun kolom system_type_name akan mengembalikan NULL, kolom ini akan mengembalikan nilai 240.
suggested_system_type_name	nvarchar (256) NULL	Berisi nama jenis data. Menyertakan argumen (seperti panjang, presisi, skala) yang ditentukan untuk jenis data parameter. Jika jenis data adalah jenis alias yang ditentukan pengguna, jenis sistem yang mendasar ditentukan di sini. Jika merupakan jenis data yang ditentukan pengguna CLR, NULL dikembalikan dalam kolom ini. Jika jenis parameter tidak dapat disimpulkan, NULL dikembalikan.
suggested_max_length	smallint NOT NULL	Lihat sys.columns. untuk deskripsi kolom max_length .
suggested_precision	tinyint NOT NULL	Lihat sys.columns. untuk deskripsi kolom presisi.
suggested_scale	tinyint NOT NULL	Lihat sys.columns. untuk deskripsi kolom skala.
suggested_user_type_id	int NULL	Untuk jenis CLR dan alias, berisi user_type_id tipe data kolom seperti yang ditentukan dalam sys.type. Jika tidak, null.
suggested_user_type_database	sysname NULL	Untuk jenis CLR dan alias, berisi nama database tempat jenis ditentukan. Jika tidak, null.
suggested_user_type_schema	sysname NULL	Untuk jenis CLR dan alias, berisi nama skema di mana jenis ditentukan. Jika tidak, null.
suggested_user_type_name	sysname NULL	Untuk jenis CLR dan alias, berisi nama jenis . Jika tidak, null.
suggested_assembly_qualified_type_name	nvarchar (4000) NULL	Untuk jenis CLR, mengembalikan nama rakitan dan kelas yang menentukan jenisnya. Jika tidak, null.
suggested_xml_collection_id	int NULL	Berisi xml_collection_id jenis data parameter seperti yang ditentukan dalam sys.columns. Kolom ini akan mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML.
suggested_xml_collection_database	sysname NULL	Berisi database tempat kumpulan skema XML yang terkait dengan jenis ini ditentukan. Kolom ini akan mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML.
suggested_xml_collection_schema	sysname NULL	Berisi skema di mana kumpulan skema XML yang terkait dengan jenis ini ditentukan. Kolom ini akan mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML.
suggested_xml_collection_name	sysname NULL	Berisi nama kumpulan skema XML yang terkait dengan tipe ini. Kolom ini akan mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML.
suggested_is_xml_document	bit NOT NULL	Mengembalikan 1 jika jenis yang dikembalikan adalah XML dan jenis tersebut dijamin sebagai dokumen XML. Jika tidak, mengembalikan 0.
suggested_is_case_sensitive	bit NOT NULL	Mengembalikan 1 jika kolom adalah jenis string peka huruf besar/kecil dan 0 jika tidak.
suggested_is_fixed_length_clr_type	bit NOT NULL	Mengembalikan 1 jika kolom berjenis CLR dengan panjang tetap dan 0 jika tidak.
suggested_is_input	bit NOT NULL	Mengembalikan 1 jika parameter digunakan di mana saja selain sisi kiri penugasan. Jika tidak, mengembalikan 0.
suggested_is_output	bit NOT NULL	Mengembalikan 1 jika parameter digunakan di sisi kiri penugasan atau diteruskan ke parameter output dari prosedur tersimpan. Jika tidak, mengembalikan 0.
formal_parameter_name	sysname NULL	Jika parameter adalah argumen untuk prosedur tersimpan atau fungsi yang ditentukan pengguna, mengembalikan nama parameter formal yang sesuai. Jika tidak, mengembalikan NULL.
suggested_tds_type_id	int NOT NULL	Untuk penggunaan internal.
suggested_tds_length	int NOT NULL	Untuk penggunaan internal.

Keterangan

sp_describe_undeclared_parameters selalu mengembalikan status pengembalian nol.

Penggunaan yang paling umum adalah ketika aplikasi diberi pernyataan Transact-SQL yang mungkin berisi parameter dan harus memprosesnya dalam beberapa cara. Contohnya adalah antarmuka pengguna (seperti ODBCTest atau RowsetViewer) di mana pengguna menyediakan kueri dengan sintaks parameter ODBC. Aplikasi harus secara dinamis menemukan jumlah parameter dan meminta pengguna untuk masing-masing parameter.

Contoh lain adalah ketika tanpa input pengguna, aplikasi harus mengulangi parameter dan mendapatkan data untuk mereka dari beberapa lokasi lain (seperti tabel). Dalam hal ini, aplikasi tidak perlu meneruskan semua informasi parameter sekaligus. Sebagai gantinya, aplikasi bisa mendapatkan semua informasi parameter dari penyedia dan mendapatkan data itu sendiri dari tabel. Kode yang menggunakan sp_describe_undeclared_parameters lebih umum dan cenderung tidak memerlukan modifikasi jika struktur data berubah nanti.

sp_describe_undeclared_parameters mengembalikan kesalahan dalam salah satu kasus berikut.

Jika input @tsql bukan batch Transact-SQL yang valid. Validitas ditentukan dengan mengurai dan menganalisis batch Transact-SQL. Kesalahan apa pun yang disebabkan oleh batch selama pengoptimalan kueri atau selama eksekusi tidak dipertimbangkan saat menentukan apakah batch Transact-SQL valid.
Jika @params bukan NULL dan berisi string yang bukan string deklarasi yang valid secara sintis untuk parameter, atau jika berisi string yang mendeklarasikan parameter apa pun lebih dari satu kali.
Jika input batch Transact-SQL mendeklarasikan variabel lokal dengan nama yang sama dengan parameter yang dideklarasikan dalam @params.
Jika pernyataan mereferensikan tabel sementara.
Kueri menyertakan pembuatan tabel permanen yang kemudian dikueri.

Jika @tsql tidak memiliki parameter, selain yang dinyatakan dalam @params, prosedur mengembalikan tataan hasil kosong.

Catatan

Anda harus mendeklarasikan variabel sebagai variabel Transact-SQL skalar, atau kesalahan muncul.

Algoritma Pemilihan Parameter

Untuk kueri dengan parameter yang tidak dinyatakan, pengurangan jenis data untuk parameter yang tidak dinyatakan berlangsung dalam tiga langkah.

Langkah 1

Langkah pertama dalam pengurangan jenis data untuk kueri dengan parameter yang tidak dinyatakan adalah menemukan jenis data semua sub-ekspresi yang jenis datanya tidak bergantung pada parameter yang tidak dinyatakan. Jenis dapat ditentukan untuk ekspresi berikut:

Kolom, konstanta, variabel, dan parameter yang dideklarasikan.
Hasil panggilan ke fungsi yang ditentukan pengguna (UDF).
Ekspresi dengan jenis data yang tidak bergantung pada parameter yang tidak dinyatakan untuk semua input.

Misalnya, pertimbangkan SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 kueri. Ekspresi dbo.tbl(@p1) + c1 dan c2 memiliki jenis data, dan ekspresi @p1 dan @p2 + 2 tidak.

Setelah langkah ini, jika ada ekspresi (selain panggilan ke UDF) memiliki dua argumen tanpa jenis data, pengurangan jenis gagal dengan kesalahan. Misalnya, semua kesalahan yang dihasilkan berikut:

SELECT * FROM t1 WHERE @p1 = @p2  
SELECT * FROM t1 WHERE c1 = @p1 + @p2  
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3)

Contoh berikut tidak menghasilkan kesalahan:

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3)

Langkah 2

Untuk parameter yang tidak dinyatakan tertentu @p, algoritma pengurangan jenis menemukan ekspresi terdarah E(@p) yang berisi @p dan merupakan salah satu hal berikut:

Argumen ke operator perbandingan atau penugasan.
Argumen ke fungsi yang ditentukan pengguna (termasuk UDF bernilai tabel), prosedur, atau metode.
Argumen ke klausa NILAI dari pernyataan INSERT .
Argumen ke CAST atau CONVERT.

Algoritma pengurangan jenis menemukan jenis data target TT(@p) untuk E(@p). Jenis data target untuk contoh sebelumnya adalah sebagai berikut:

Jenis data dari sisi lain perbandingan atau penugasan.
Jenis data yang dideklarasikan dari parameter tempat argumen ini diteruskan.
Tipe data kolom tempat nilai ini disisipkan.
Jenis data tempat pernyataan ditransmisikan atau dikonversi.

Misalnya, pertimbangkan SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) kueri. Kemudian E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) adalah jenis data pengembalian dbo.tbl yang dideklarasikan, dan TT(@p2) adalah jenis data parameter yang dideklarasikan untuk dbo.tbl.

Jika @p tidak terkandung dalam ekspresi apa pun yang tercantum di awal langkah 2, algoritma pengurangan jenis menentukan bahwa E(@p) adalah ekspresi skalar terbesar yang berisi @p, dan algoritma pengurangan jenis tidak menghitung tipe data target TT(@p) untuk E(@p). Misalnya, jika kueri adalah SELECT @p + 2 maka E(@p) = @p + 2, dan tidak ada TT(@p).

Langkah 3

Sekarang setelah E(@p) dan TT(@p) diidentifikasi, algoritma pengurangan jenis menyimpulkan jenis data untuk @p dengan salah satu dari dua cara berikut:

Pengurangan sederhana

Jika E(@p) = @p dan TT(@p) ada, yaitu, jika @p secara langsung merupakan argumen ke salah satu ekspresi yang tercantum di awal langkah 2, algoritma pengurangan jenis menyimpulkan jenis data @p menjadi TT(@p). Misalnya:
```
SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3)  
```
Jenis data untuk @p1, @p2, dan @p3 akan menjadi jenis data c1, jenis data pengembalian dbo.tbl, dan jenis data parameter untuk dbo.tbl masing-masing.

Sebagai kasus khusus, jika @p adalah argumen ke <operator , , ><=, atau >=, aturan pengurangan sederhana tidak berlaku. Algoritma pengurangan jenis akan menggunakan aturan pengurangan umum yang dijelaskan di bagian berikutnya. Misalnya, jika c1 adalah kolom karakter tipe data(30), pertimbangkan dua kueri berikut:
```
SELECT * FROM t WHERE c1 = @p  
SELECT * FROM t WHERE c1 > @p  
```
Dalam kasus pertama, algoritma pengurangan jenis menyimpulkan char(30) sebagai jenis data untuk @p sesuai aturan sebelumnya dalam topik ini. Dalam kasus kedua, algoritma pengurangan jenis menyimpulkan varchar (8000) sesuai dengan aturan pengurangan umum di bagian berikutnya.
Pengurangan umum

Jika pengurangan sederhana tidak berlaku, jenis data berikut dipertimbangkan untuk parameter yang tidak dinyatakan:
- Jenis data bilangan bulat (bit, tinyint, smallint, int, bigint)
- Jenis data uang (smallmoney, money)
- Jenis data floating-point (float, real)
- numerik(38, 19) - Jenis data numerik atau desimal lainnya tidak dipertimbangkan.
- varchar(8000), varchar(max), nvarchar(4000), dan nvarchar(max) - Jenis data string lainnya (seperti teks, char(8000), nvarchar(30), dll.) tidak dipertimbangkan.
- varbinary(8000) dan varbinary(max) - Jenis data biner lainnya tidak dipertimbangkan (seperti gambar, biner(8000), varbinary(30), dll.).
- tanggal, waktu(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Jenis tanggal dan waktu lainnya, seperti waktu(4), tidak dipertimbangkan.
- aql_variant
- xml
- Jenis yang ditentukan sistem CLR (hierarkiid, geometri, geografi)
- Jenis yang ditentukan pengguna CLR

Kriteria Pilihan

Dari jenis data kandidat, tipe data apa pun yang akan membatalkan kueri ditolak. Dari jenis data kandidat yang tersisa, algoritma pengurangan jenis memilih satu sesuai dengan aturan berikut.

Jenis data yang menghasilkan jumlah terkecil konversi implisit dalam E(@p) dipilih. Jika jenis data tertentu menghasilkan jenis data untuk E(@p) yang berbeda dari TT(@p), algoritma pengurangan jenis menganggap ini sebagai konversi implisit ekstra dari jenis data E(@p) ke TT(@p).

Misalnya:
```
SELECT * FROM t WHERE Col_Int = Col_Int + @p  
```
Dalam hal ini, E(@p) Col_Int + @p dan TT(@p) adalah int. int dipilih untuk @p karena tidak menghasilkan konversi implisit. Pilihan jenis data lainnya menghasilkan setidaknya satu konversi implisit.
Jika beberapa jenis data mengikat untuk jumlah konversi terkecil, jenis data dengan prioritas yang lebih besar digunakan. Misalnya
```
SELECT * FROM t WHERE Col_Int = Col_smallint + @p  
```
Dalam hal ini, int dan smallint menghasilkan satu konversi. Setiap jenis data lainnya menghasilkan lebih dari satu konversi. Karena int lebih diutamakan daripada kecil, int digunakan untuk @p. Untuk informasi selengkapnya tentang prioritas jenis data, lihat Prioritas Tipe Data (Transact-SQL).

Aturan ini hanya berlaku jika ada konversi implisit antara setiap jenis data yang mengikat sesuai dengan aturan 1 dan jenis data dengan prioritas terbesar. Jika tidak ada konversi implisit, pengurangan jenis data gagal dengan kesalahan. Misalnya dalam kueri SELECT @p FROM t, pengurangan jenis data gagal karena jenis data apa pun untuk @p akan sama baiknya. Misalnya, tidak ada konversi implisit dari int ke xml.
Jika dua jenis data serupa mengikat di bawah aturan 1, misalnya varchar(8000) dan varchar(max), jenis data yang lebih kecil (varchar(8000)) dipilih. Prinsip yang sama berlaku untuk jenis data nvarchar dan varbinary .
Untuk tujuan aturan 1, algoritma pengurangan jenis lebih memilih konversi tertentu sebagai lebih baik daripada yang lain. Konversi secara berurutan dari yang terbaik ke terburuk adalah:
1. Konversi antara jenis data dasar yang sama dengan panjang yang berbeda.
2. Konversi antara versi panjang tetap dan panjang variabel dari jenis data yang sama (misalnya, karakter ke varchar).
3. Konversi antara NULL dan int.
4. Konversi lainnya.

Misalnya, untuk kueri SELECT * FROM t WHERE [Col_varchar(30)] > @p, varchar(8000) dipilih karena konversi (a) adalah yang terbaik. Untuk kueri SELECT * FROM t WHERE [Col_char(30)] > @p, varchar(8000) masih dipilih karena menyebabkan konversi tipe (b), dan karena pilihan lain (seperti varchar(4000)) akan menyebabkan konversi jenis (d).

Sebagai contoh akhir, diberi kueri SELECT NULL + @p, int dipilih untuk @p karena menghasilkan konversi tipe (c).

Izin

Memerlukan izin untuk menjalankan argumen @tsql.

Contoh

Contoh berikut mengembalikan informasi seperti jenis data yang diharapkan untuk parameter dan @name yang tidak dinyatakan@id.

sp_describe_undeclared_parameters @tsql =   
N'SELECT object_id, name, type_desc   
FROM sys.indexes  
WHERE object_id = @id OR name = @name'

@id Ketika parameter disediakan sebagai @params referensi, parameter dihilangkan @id dari kumpulan hasil dan hanya parameter yang @name dijelaskan.

sp_describe_undeclared_parameters @tsql =   
N'SELECT object_id, name, type_desc   
FROM sys.indexes  
WHERE object_id = @id OR NAME = @name',  
@params = N'@id int'

Share via