Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.
There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons.
- Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal “best” approach.
- The Iterative Method. Pass a delimited string and loop through it.
- Using the CLR. SQL Server 2005 and higher from .NET languages only.
- XML. Very good for inserting many rows; may be overkill for SELECTs.
- Table of Numbers. Higher performance/complexity than simple iterative method.
- Fixed-length Elements. Fixed length improves speed over the delimited string
- Function of Numbers. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.
- Recursive Common Table Expression (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.
- Dynamic SQL. Can be slow and has security implications.
- Passing the List as Many Parameters. Tedious and error prone, but simple.
- Really Slow Methods. Methods that uses charindex, patindex or LIKE.
I really can’t recommend enough to read the article to learn about the tradeoffs among all these options.