在IN条件不起作用的商店程序 [英] Where IN condition not working Store procedures

问题描述

团队，

我是这个页面的新手.当我尝试在sql 2005中制作SP时，我面临两个问题.

 创建  proc 部门( @部门  varchar ( 30 ))
为
选择 * 来自 test1
其中 [部门]  in (( @部门 )

当我传递一个值即
时，上面的sp正在工作

  exec 部门'  a ' 

但是当我尝试执行更多时，@ department中的一个值.它无法正常工作，即

  exec 部门'  a '，'  b' 

但是当我尝试检查我的查询时.它给了我结果

 选择 * 来自 test1
其中 [部门] 在中('  a'，'  b')

为什么它不能在SP中使用.

我想通过ASP.NET通过C#

解决方案

传递@department，您的存储过程被定义为仅接受一个参数.如果您尝试传递多个参数，那么它将无法正常工作.

如果需要将可变数量的参数传递给sp，则可能必须在参数列表中包含一些定界符，并将该列表作为单个参数传递给SP.

即

  exec 部门'  a ，b' 

(观看单引号)


并且在您的sp内部，您必须从此逗号分隔的列表中提取值，然后在实际查询中使用它们.

您可以尝试以下方法吗?

  exec 部门'  a ，b' 

应该可以.

问候
塞巴斯蒂安

嗨塞巴斯蒂安，

它不起作用:(

执行部(``a，b'')

a，b是带有抬头部门的表的值

Hi Team,

I am very new to this page. I am facing two issues when i tried to make a SP in sql 2005.

CREATE proc Dept (@department varchar(30))      
as      
	Select * from test1      
	where [Department] in (@department)

the above sp is working find when i am passing one value i.e

exec dept 'a'

But when i try to execute more then one value in @department. Its not working fine i.e.

exec dept 'a','b'

but when i tried to check my query. its giving me results

Select * from test1      
	where [Department] in ('a','b')

Why its not working in SP.

The second this i would like to pass @department from asp.net through C#

解决方案

Your stored procedure is defined to accept only one parameter. If you try to pass multiple parameters then it wont work.

If you need to pass variable number of parameters to sp then perhaps you will have to imclude some delimiter in your parameter list and pass that list as single patameter into the SP.

i.e.

exec dept 'a, b' 

(watch the single quote)


and inside your sp you will have to extract the values from this comma separated list and then you them in the actual query.

Hi,

Can you try it as follows?

exec dept 'a,b'

It should work.

Regards
Sebastian

Hi Sebastian,

Its not working :(

Exec Dept (''a,b'')

a,b are the values of a table with Header Department

这篇关于在IN条件不起作用的商店程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！