Any way to build string to be used in WHERE IN()?

I am using SQL Server 2005.

I am using some string functions, to generate a string back that is always in this format:

Number,Number,Number,Number

I am attempting to use this generated string like so:

select LastName from User 
Where ID in (
subtring(<returning the format shown above>)
)

I keep getting the error: Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric.

This makes sense, that if WHERE IN() is passed in a string, that it would not work - since usually it is accepting comma seperated true integers.

Is there any way to accomplish what开发者_运维问答 I'm trying here?

Thank you!

Dynamic SQL should do the trick.

declare @ids varchar(100) set @ids = '1,2,3,4,5,6,7'

declare @sql nvarchar(200) set @sql = N'select LastName from User Where ID in (' + @ids + N')'

exec sp_executesql @sql

See http://msdn.microsoft.com/en-us/library/aa933299(v=sql.80).aspx