Select records with order of IN clause

Question

I have

SELECT * FROM Table1 WHERE Col1 IN(4,2,6)

I want to select and return the records with the specified order which i indicate in the IN clause (first display record with Col1=4, Col1=2, ...)

I can use

SELECT * FROM Table1 WHERE Col1 = 4
UNION ALL
SELECT * FROM Table1 WHERE Col1 = 6 , .....

but I don't want to use that, cause I want to use it as a stored procedure and not auto generated.

Answer 1

I know it's a bit late but the best way would be

SELECT *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')

Or

SELECT CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')s_order,
       *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY s_order

Answer 2

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.