Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
Dear Developer,

I want to send multiple Id into storedprocedure parameter using IN Query.

SQL
CREATE  procedure [dbo].[sp_proc_select]      
( 
   @bankId int
)
as 
begin
   select * from tbl_bankmaster where bank_id in (@bankId)
end


if I execute this procedure with sending multiple id 5 and 28

exec sp_proc_select (5,28)

Then i got error syntax error near the keyword "(".

There is anyway to send multiple Id to stored procedure parameter using In Query or any alternate method?

Thanks in advanced
regards,
Ravi Sharma
Posted
Updated 10-Jan-12 18:43pm
v3
Comments
chandrashekhar racharla 11-Jan-12 0:12am    
Hi ravi You have only one parameter in the procedure so how can you pass two parameters at the time

There are multiple ways of doing the same. one option is to use the dynamic sql query. Example:

SQL
CREATE procedure [dbo].[sp_proc_select]
(
@bankId varchar(4096)
)
as
begin
   exec('select * from tbl_bankmaster where bank_id in (' + @bankId + ')')
end

exec sp_proc_select ('5,28')


Another option is to pass the XML as parameter and then use in the query or you can also use split function inside the stored proc if you don't want dynamic query....
 
Share this answer
 
v2
Comments
m@dhu 11-Jan-12 0:18am    
simple 5!
Ravi Sharma 2 11-Jan-12 0:28am    
Dear Om,

When i execute above Quarry ,exec sp_proc_select ('5,28')
then its give the error "Incorrect syntax near '5,28'." and not give the output.
Om Prakash Pant 11-Jan-12 0:57am    
call the SP as follows:
exec sp_proc_select '5,28'
Om Prakash Pant 11-Jan-12 0:58am    
Here is another example:

CREATE TABLE tbl_bankmaster (bank_id int, bank_name varchar(100),status char(1))

insert into tbl_bankmaster values (5, 'Bank5',0);
insert into tbl_bankmaster values (28, 'Bank28',0);

create procedure [dbo].[sp_proc_select] (@bankId varchar(4096))
as
begin
exec ('update tbl_bankmaster set status = 1 where bank_id in (' + @bankId + ')')
end

exec sp_proc_select '5,28'

SELECT * FROM tbl_bankmaster
Member 12240752 5-Feb-20 1:26am    
Thanks, Om Prakash.
It is Working
And a third way is to use table type parameter and use that table in join with tbl_bankmaster. For more info: Using Table-Valued Functions in SQL Server[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900