Pass the "in" parameter dynamically - ms sql server

Question

I have the following ms-sql query

select * from category where categoryId in (1)

Now 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.

Jonas Høgh · Accepted Answer · 2011-08-19 09:14:48Z

2

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

answered Aug 19, 2011 at 9:14

Jonas Høgh

10.9k1 gold badge29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Eriksson · Accepted Answer · 2011-08-19 09:11:27Z

1

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))

answered Aug 19, 2011 at 9:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Pass the "in" parameter dynamically - ms sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related