如何将多个ID发送到SQL存储过程 [英] How to send multiple Id to the SQL Stored procedure

问题描述

亲爱的开发人员，

我想使用IN Query将多个ID发送到storedprocedure参数中.

Dear Developer,

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

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

如果我通过发送多个ID 5和28执行此过程，则

exec sp_proc_select(5,28)

然后我在关键字("附近出现了错误语法错误.

无论如何，都可以使用In Query或任何其他方法将多个ID发送到存储过程参数?

感谢进阶
问候，
Ravi Sharma

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

推荐答案

可以通过多种方式进行相同操作.一种选择是使用动态sql查询.示例:

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

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

另一种选择是将XML作为参数传递，然后在查询中使用，或者，如果您不希望动态查询，也可以在存储的proc中使用split函数.

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

第三种方法是使用表类型参数，并将该表与tbl_bankmaster结合使用.有关更多信息:在SQL Server中使用表值函数 [

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[^]

这篇关于如何将多个ID发送到SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！