In SQL Server, a string separated by a comma "," is converted to a table set and applied to the in condition

In SQL Server, a string that is separated by a comma "," is converted to a table and applied with the in condition

Select  from where inch (1,2,3)

Such statements and commonly used, but if in the next one of the next-to-last is a variable what to do, it is common to use string connection to construct the SQL statement

string aa= "1,2,3"; string sqltxt= "selectfromwhere in (" +aa+ ")";

Then execute Sqltxt

The risk is that there is a SQL injection vulnerability. So how do you use variables in the in condition? It is possible to convert a string such as "one-way" to a temporary table with one column, 3 rows, and one item in each row (separated by commas)

This function can be written like this:

Create Function strtotable (@str varchar ( +)) Returns @tableName Table (str2table varchar ( -) as– The function is used to turn a comma-delimited number of data strings into a single column of a table, such as a string '1,2,3,4,5' will program a table, this table BeginSet@str = @str +', ' Declare @insertStr varchar ( -) – The first string after interception Declare @newstr varchar ( +) – The string remaining after the first string is interceptedSet@insertStr = Left (@str, charindex (', ', @str)-1) Set@newstr = Stuff (@str,1, CHARINDEX (', ', @str), ") Insert @tableName Values (@insertStr) while(Len (@newstr) >0) BeginSet@insertStr = Left (@newstr, charindex (', ', @newstr)-1) Insert @tableName Values (@insertStr)Set@newstr = Stuff (@newstr,1, CHARINDEX (', ', @newstr), ") End Return End

And then the SQL statement can do that.

Declare str vchar (+); - -Define the str variable set str= '1,2,3'; -- Assigning a variable to a selectfromwhere in (theselect from Strtotable (@str))

Explain:

A. Select Str2table from strtotable (1,2,3)--Call function strtotable (1,2,3), execute the result is: (by the comma ", "Splits a string (1,2,3) into a table result set of a field)

Str2table
1
2
3

B. Select  from where inch (Select from strtotable (1,2,3)) is equivalent to executing a Select  from where inch (1,2,3)

Last: Attach an actual project SQL example

DECLARE @str varchar (+)-  - define variable Selectfromwhere Qyid = ${qyid}-- assigning values to variables Select xsqxtbzd+',' from [dbo].[ D_HYLB]where in (select from Strtotable (@str))  -- Call function        FOR XML Path (");  --Presenting the query result set as XML (associating the result set to a string in some form)

SQL Server Converts a string separated by a comma "," to a table set and applied to an in condition