Mengapa Menggunakan Pembolehubah VARCHAR dalam Klausa SQL Server IN Menyebabkan Ralat Penukaran?

SQL Server IN Klausa dan Pembolehubah VARCHAR: Panduan Penyelesaian Masalah

Masalah:

Kod SQL Server berikut menjana ralat penukaran:

<code class="language-sql">DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3, 4, 22' -- Note: Strings are now enclosed in single quotes

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)</code>

Penjelasan Ralat:

Klausa IN menjangkakan senarai nilai yang dipisahkan koma daripada jenis data yang sama seperti lajur yang dibandingkan (dalam kes ini, Id, yang mungkin merupakan integer). Pembolehubah @ExcludedList ialah VARCHAR(MAX), mengandungi rentetan. SQL Server cuba secara tersirat menukar keseluruhan rentetan kepada integer, yang membawa kepada kegagalan kerana rentetan itu termasuk koma.

Penyelesaian:

Elakkan menggunakan pembolehubah rentetan terus dalam klausa IN. Sebaliknya, gunakan pendekatan bernilai jadual:

<code class="language-sql">DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3, 4, 22'

DECLARE @IntExcludedList TABLE (ID INT);
INSERT INTO @IntExcludedList (ID)
SELECT value FROM STRING_SPLIT(@ExcludedList, ',');

SELECT * FROM A WHERE Id NOT IN (SELECT ID FROM @IntExcludedList);</code>

Kod yang dipertingkatkan ini menggunakan STRING_SPLIT, fungsi terbina dalam yang memisahkan rentetan yang dipisahkan koma dengan cekap kepada baris individu. Nilai setiap baris kemudiannya dimasukkan ke dalam pembolehubah jadual integer @IntExcludedList, membenarkan perbandingan yang betul dengan lajur Id. Kaedah ini menghalang penukaran tersirat dan memastikan klausa IN berfungsi seperti yang dimaksudkan. Perhatikan penggunaan petikan tunggal di sekitar nilai rentetan dalam @ExcludedList.

Pendekatan ini lebih cekap dan boleh dibaca daripada penyelesaian asal yang menggunakan SUBSTRING dan PATINDEX. Ia memanfaatkan keupayaan manipulasi rentetan terbina dalam SQL Server untuk prestasi optimum.

Atas ialah kandungan terperinci Mengapa Menggunakan Pembolehubah VARCHAR dalam Klausa SQL Server IN Menyebabkan Ralat Penukaran?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!