Bagikan melalui

sp_describe_undeclared_parameters (T-SQL)

  • Artikel

Berlaku untuk: Titik akhir analitik SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics 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'params [ , ...n ]' ]

Catatan

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

Argumen

[ @tsql = ] 'tsql'

Satu atau beberapa pernyataan Transact-SQL. @tsql mungkin nvarchar(n) atau nvarchar(max).

[ @params = ] N'params'

@params menyediakan string deklarasi untuk parameter untuk batch Transact-SQL, mirip dengan cara sp_executesql kerjanya. @params mungkin nvarchar(n) atau nvarchar(max).

String yang berisi definisi semua parameter yang disematkan dalam @tsql. 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.

Mengembalikan nilai kode

sp_describe_undeclared_parameters selalu mengembalikan status nol pada keberhasilan. Jika prosedur melemparkan kesalahan dan prosedur disebut sebagai RPC, status pengembalian diisi oleh jenis kesalahan seperti yang dijelaskan dalam error_type kolom 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 Berisi posisi ordinal parameter dalam tataan hasil. Posisi parameter pertama ditentukan sebagai 1. Tidak dapat diubah ke null.
name nama sysname Berisi nama parameter. Tidak dapat diubah ke null.
suggested_system_type_id int system_type_id Berisi jenis data parameter seperti yang ditentukan dalam sys.types.

Untuk jenis CLR, meskipun system_type_name kolom mengembalikan NULL, kolom ini mengembalikan nilai 240. Tidak dapat diubah ke null.
suggested_system_type_name nvarchar(256) 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 jenis data yang ditentukan pengguna CLR, NULL dikembalikan dalam kolom ini. Jika jenis parameter tidak dapat disimpulkan, NULL dikembalikan. Dapat diubah ke null.
suggested_max_length smallint Lihat sys.columns. untuk max_length deskripsi kolom. Tidak dapat diubah ke null.
suggested_precision kecil Lihat sys.columns. untuk deskripsi kolom presisi. Tidak dapat diubah ke null.
suggested_scale kecil Lihat sys.columns. untuk deskripsi kolom skala. Tidak dapat diubah ke null.
suggested_user_type_id int Untuk jenis CLR dan alias, berisi user_type_id jenis data kolom seperti yang ditentukan dalam sys.types. Jika tidak, adalah NULL. Dapat diubah ke null.
suggested_user_type_database nama sysname Untuk jenis CLR dan alias, berisi nama database tempat jenis ditentukan. Jika tidak, adalah NULL. Dapat diubah ke null.
suggested_user_type_schema nama sysname Untuk jenis CLR dan alias, berisi nama skema di mana jenis ditentukan. Jika tidak, adalah NULL. Dapat diubah ke null.
suggested_user_type_name nama sysname Untuk jenis CLR dan alias, berisi nama jenis . Jika tidak, adalah NULL.
suggested_assembly_qualified_type_name nvarchar(4000) Untuk jenis CLR, mengembalikan nama rakitan dan kelas yang menentukan jenisnya. Jika tidak, adalah NULL. Dapat diubah ke null.
suggested_xml_collection_id int xml_collection_id Berisi jenis data parameter seperti yang ditentukan dalam sys.columns. Kolom ini mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML. Dapat diubah ke null.
suggested_xml_collection_database nama sysname Berisi database tempat kumpulan skema XML yang terkait dengan jenis ini ditentukan. Kolom ini mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML. Dapat diubah ke null.
suggested_xml_collection_schema nama sysname Berisi skema di mana kumpulan skema XML yang terkait dengan jenis ini ditentukan. Kolom ini mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML. Dapat diubah ke null.
suggested_xml_collection_name nama sysname Berisi nama kumpulan skema XML yang terkait dengan tipe ini. Kolom ini mengembalikan NULL jika jenis yang dikembalikan tidak terkait dengan kumpulan skema XML. Dapat diubah ke null.
suggested_is_xml_document bit Mengembalikan 1 jika jenis yang dikembalikan adalah XML dan jenis tersebut dijamin sebagai dokumen XML. Jika tidak, mengembalikan 0. Tidak dapat diubah ke null.
suggested_is_case_sensitive bit Mengembalikan 1 jika kolom adalah jenis string peka huruf besar/kecil dan 0 jika tidak. Tidak dapat diubah ke null.
suggested_is_fixed_length_clr_type bit Mengembalikan jika kolom berjenis 1 CLR dengan panjang tetap dan 0 jika tidak. Tidak dapat diubah ke null.
suggested_is_input bit Mengembalikan 1 jika parameter digunakan di mana saja selain sisi kiri penugasan. Jika tidak, mengembalikan 0. Tidak dapat diubah ke null.
suggested_is_output bit Mengembalikan 1 jika parameter digunakan di sisi kiri penugasan atau diteruskan ke parameter output dari prosedur tersimpan. Jika tidak, mengembalikan 0. Tidak dapat diubah ke null.
formal_parameter_name nama sysname Jika parameter adalah argumen untuk prosedur tersimpan atau fungsi yang ditentukan pengguna, mengembalikan nama parameter formal yang sesuai. Jika tidak, mengembalikan NULL. Dapat diubah ke null.
suggested_tds_type_id int Untuk penggunaan internal. Tidak dapat diubah ke null.
suggested_tds_length int Untuk penggunaan internal. Tidak dapat diubah ke null.

Keterangan

sp_describe_undeclared_parameters selalu mengembalikan status 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.

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

  • @params tidak 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.

  • Input batch Transact-SQL mendeklarasikan variabel lokal dengan nama yang sama dengan parameter yang dideklarasikan dalam @params.

  • Pernyataan ini mereferensikan tabel sementara.

  • Kueri menyertakan pembuatan tabel permanen yang kemudian dikueri.

Jika @tsql tidak memiliki parameter, selain parameter 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: Temukan jenis data sub-ekspresi

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, serta 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: Temukan ekspresi paling dalam

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

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

Algoritma pengurangan jenis menemukan jenis TT(@p) data target 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, , TT(@p1) E(@p2) = @p2 + c1adalah jenis data pengembalian yang dideklarasikan dari dbo.tbl, dan TT(@p2) merupakan 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 jenis TT(@p) data target untuk E(@p). Misalnya, jika kueri adalah SELECT @p + 2 , E(@p) = @p + 2dan tidak TT(@p)ada .

Langkah 3: Menyimpulkan jenis data

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 @p jenis menyimpulkan jenis data menjadi TT(@p). Contohnya:

    SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);

    Jenis data untuk @p1, , dan @p3 akan menjadi jenis c1data , jenis data pengembalian , dbo.tbldan jenis data parameter untuk dbo.tbl @p2masing-masing.

    Sebagai kasus khusus, jika @p adalah argumen ke <operator , , ><=, atau >= , 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 sesuai @p aturan sebelumnya dalam artikel 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.).

    • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Jenis tanggal dan waktu lainnya, seperti time(4), tidak dipertimbangkan.

    • aql_variant

    • xml

    • Jenis yang ditentukan sistem CLR (hierarkiid, geometri, geografi)

    • Jenis yang ditentukan pengguna CLR

Kriteria seleksi

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.

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

    Contohnya:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p;

    Dalam hal ini, E(@p) adalah Col_Int + @p dan TT(@p) int. int dipilih @p karena tidak menghasilkan konversi implisit. Pilihan jenis data lainnya menghasilkan setidaknya satu konversi implisit.

  2. Jika beberapa jenis data mengikat untuk jumlah konversi terkecil, jenis data dengan prioritas yang lebih besar digunakan. Contohnya:

    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 smallint, int digunakan untuk @p. Untuk informasi selengkapnya tentang prioritas jenis data, lihat Prioritas jenis data.

    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 @p data apa pun akan sama baiknya. Misalnya, tidak ada konversi implisit dari int ke xml.

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

  4. 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 @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.

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

EXEC 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';

Konten terkait