Pass the "in" parameter dynamically - ms sql server

I have the following ms-sql query

select * from category where categoryId in (1)

No开发者_如何学JAVAw instead of 1, I need to pass more than one categoryId dynamically using parameters. Something like this:

select * from category where categoryId in (@catId)

and value of @catId would be something like '1,2'. When I tried this I got an error saying

Error converting data type varchar to bigint.

How can I pass more than one categoryId.

Thanks in advance.

You can use Table-valued parameters, a new feature in SQL Server 2008

You can use the XML data type instead to pass a list of ID's.

declare @catId xml = '<i>1</i><i>2</i><i>3</i>'

select *
from category
where categoryid in (select T.N.value('.', 'int')
                     from @catId.nodes('/i') as T(N))