sql server - how to pass multiple values in single parameter and how to convert varchar value into integers -

create table students ( id int,   name  varchar(20) )  insert students(id,name)values(1,'danny') insert students(id,name)values(2,'dave') insert students(id,name)values(3,'sue') insert students(id,name)values(4,'jack') insert students(id,name)values(5,'rita') insert students(id,name)values(6,'sarah') 

this stored procedure

alter procedure emp_sp  ( @std_id varchar(500), @std_name varchar(500) ) begin  select *from students s s.id in(convert(int,@std_id) ,',') ,  s.name in(@std_name) end go 

here execute manually

exec dbo.emp_sp @std_id='1,2,3', @std_name='"danny","dave","sue"' 

but error:

msg 245, level 16, state 1, procedure emp_sp, line 8
conversion failed when converting varchar value ',' data type int.

anyone can guide me.

to current approach working, need use dynamic sql, incredibly fragile , prone sql injection attacks. example of here

the better way through table valued parameters:

create type ttstudentids table (   id int ); go  create type ttstudentnames table (   name varchar(20) ); go  create procedure dbo.emp_sp  (    @stdids ttstudentids readonly,    @stdnames ttstudentnames readonly ) begin     select s.id, s.name    students s    inner join @stdids si     on s.id = si.id     union     select s.id, s.name    students s    inner join @stdnames  sn    on s.name = sn.name; end go 

and called so:

declare @ids ttstudentids; declare @names ttstudentnames;  insert @ids values (1),(2),(3); insert @names values ('danny'),('dave'),('sue');  exec dbo.emp_sp @ids, @names; 

sqlfiddle here