TSQL: Checking if an ID is in a Comma Separated String

Ok, so the scenario is that we have a list of IDs, maybe from a checkboxlist, and we want to get the records that match those IDs from a table. So for the sake of this example, I just assume that the list of IDs is passed to my stored procedure as a varchar(8000) string. Using Northwind as an example database, heres an example of how an SP that gets products could look like:

CREATE PROCEDURE GetProducts
@ListOfProductsAsCSVString varchar(8000)

AS

SET @ListOfProductsAsCSVString = ',' + @ListOfProductsAsCSVString + ',';

SELECT ProductID, ProductName FROM Products
WHERE CHARINDEX(',' + CAST(ProductID as varchar(10)) + ',', @ListOfProductsAsCSVString ) > 0;

So what I do is to first append a comma before and after the CSV-list. This is because I need to search for somthing that starts with a comma and ends with a comma, and usually a CSV-list doesn’t have a comma before the first element or after the last one. Then, in my select, I search using the CHARINDEX function for the ProductID prefixed and postfixed by a comma.

Now, please be aware that this could lead to a possible SQL Injection attack, if you use this procedure uncritically without validating the input before passing it to this stored procedure, so use with caution.