How to parse a VARCHAR passed to a stored procedure in SQL Server? -

i have 2 tables tbl_products , tbl_brands, both joined on brandid.

i have stored procedure should return products belong brand ids passed parameter.

my code follows.

create proc sp_returnprdoucts     @brandids varchar(500) = '6,7,8' begin     select *      tbl_products p      join tbl_brands b on p.productbrandid = b.brandid      b.brandid in (@brandids) end 

but giving error brandid int , @brandids varchar

when hard code way follows works fine , returns desired data db ..

create proc sp_returnprdoucts     @brandids varchar(500) = '6,7,8' begin     select *      tbl_products p      join tbl_brands b on p.productbrandid = b.brandid      b.brandid in (6,7,8) end 

any :)

another alternative use 'indirection' (as i've called it)

you can do..

create proc sp_returnprdoucts @brandids varchar(500) = '6,7,8' begin     if (isnumeric(replace(@brandids,',',''))=1)      begin         exec('select * tbl_products p join tbl_brands b on p.productbrandid=b.brandid b.brandid in ('+@brandids+')')     end end 

this way select statement built string, executed.

i've added validation ensure string being passed in purely numeric (after removing commas)