I have sample table and query with the issue described here,
CREATE TABLE test
(
ID INT IDENTITY(1, 1),
NAME VARCHAR(250),
VALUE float
)
INSERT INTO test(NAME,[VALUE])VALUES('A',100)
INSERT INTO test(NAME,[VALUE])VALUES('B',200)
INSERT INTO test(NAME,[VALUE])VALUES('C',200)
SELECT * FROM test WHERE ID IN (2,1,3)
ID NAME VALUE
----------- --------- ----------------
1 A 100
2 B 200
3 C 200
QUERY : when I pass (2,1,3) in WHERE clause it should give result in same ORDER as below :
ID NAME VALUE
----------- --------- ----------------
2 B 200
1 A 100
3 C 200
If you grab a copy of DelimitedSplit8K you could do this:
-- assuming these values come in as a parameter:
DECLARE @searchString varchar(100) = '2,1,3';
-- solution using delimitedSplit8K
SELECT t.ID, t.Name, t.VALUE
FROM dbo.test t
JOIN dbo.delimitedSplit8K(@searchString,',') s ON s.item = t.id
ORDER BY s.itemNumber;
Results:
ID Name VALUE
----------- ----- ------
2 B 200
1 A 100
3 C 200
What makes this technique particularly wonderful is how, if you examine the query execution plan, there is no Sort Operator.