In SQL queries, we usually use IN to query results based on the set of known IDs. The ID set is provided directly after the IN statement or a subquery is followed by the IN statement.
In SQL queries, we usually use IN to query results based on the set of known IDs. The ID set is provided directly after the IN statement or a subquery is followed by the IN statement.
As follows:
The Code is as follows:
SELECT * FROM Orders
WHERE OrderGUID IN ('bc71d821-9e25-47108bf5e-009822a3fc1d ', 'f2212304-51D4-42C9-AD35-5586A822258E ')
It can be seen that each ID needs to be enclosed IN single quotes directly after the IN and the ID set. In actual application, a string of guids is collected on the interface, separated by commas (,). If the string is uploaded as a parameter to a stored procedure for execution, the generated statement is as follows:
The Code is as follows:
SELECT * FROM Orders
WHERE OrderGUID IN ('bc71d821-9e25-47108bf5e-009822a3fc1d, F2212304-51D4-42C9-AD35-5586A822258E ')
In this way, the correct results cannot be queried.
In general, we solve this problem by using a split function to process the input string. The final result is a table and then the table can be self-queried, as shown below:
The Code is as follows:
DECLARE @ IDs VARCHAR (4000)
SET @ IDs = 'bc71d821-9e25-47108bf5e-009822a3fc1d, F2212304-51D4-42C9-AD35-5586A822258E'
DECLARE @ temp TABLE (str VARCHAR (50 ))
Insert into @ temp
SELECT * FROM dbo. Split (@ IDs ,',')
SELECT * FROM Orders WHERE OrderGUID IN (SELECT str FROM @ temp)
Of course, the split function system is not provided, so we need to write it ourselves:
The Code is as follows:
Create function Split
(
@ SourceSql varchar (8000 ),
@ StrSeprate varchar (10)
)
RETURNS @ temp TABLE (F1. VARCHAR (100 ))
AS
BEGIN
DECLARE @ I INT
SET @ SourceSql = rtrim (ltrim (@ SourceSql ))
SET @ I = charindex (@ StrSeprate, @ SourceSql)
WHILE @ I> = 1
BEGIN
INSERT @ temp VALUES (left (@ SourceSql, @ i-1 ))
SET @ SourceSql = substring (@ SourceSql, @ I + 1, len (@ SourceSql)-@ I)
SET @ I = charindex (@ StrSeprate, @ SourceSql)
END
IF @ SourceSql <>''
INSERT @ temp VALUES (@ SourceSql)
RETURN
END
It is very troublesome to do this, and it also needs to be implemented using functions. The following describes a simple method, because GUID is unique, so IN the above example, you can use LIKE instead of IN to achieve the same query effect:
The Code is as follows:
SELECT * FROM Orders
WHERE 'bc71d821-9e25-47108bf5e-009822a3fc1d, F2212304-51D4-42C9-AD35-5586A822258E'
LIKE '%' + convert (VARCHAR (40), OrderGUID) + '%'
