Parsing a comma-separated list of values into a table in SQL

You might want to select records that match any of several values. For example, you might want to get all restaurants where restaurantID is 50, 51 or 60. That's no problem in a T-SQL select statement: select * from restaurants where restaurantID in (50,51,60).

However, suppose you'd like to pass the list of restaurant IDs as a parameter to a stored procedure. Let's suppose the stored proc accepts an nvarchar parameter named @restaurantID. In our example, @restaurantID = '50,51,60'.

T-SQL does not allow something like this: select * from restaurants where restaurantID in @restaurantID.

You must get the list of restaurant IDs into a temporary table and do something like this:

declare @r table (
restaurantid int
)
-- parse comma-separated list into table here...
select * from restaurants where restaurantid in (select restaurantid from @r)

So, how do you parse the comma-separate list into a table? Like this:

declare @index int
declare @leftPart varchar(255)
while len(@restaurantidlist) > 0
begin
set @index = charindex(',', @restaurantidlist)
if @index = 0
begin
set @index = len(@restaurantidlist) + 1
end
set @leftPart = substring(@restaurantidlist, 0, @index)
set @restaurantidlist = substring(@restaurantidlist, @index + 1, len(@restaurantidlist))
insert into @r (restaurantid) values (@leftPart)
end