很奇怪的一个问题,是关于sql参数化和传where in 参数的迷惑.请大伙给讲讲吧...小弟很迷糊现在.各位园友,拉兄弟一把!!...

报错的语句:     strSql.Append(" AND cu.CurrentSalesMan in (@CurrentSalesMans)");                                

                     db.AddParameter("@CurrentSalesMans", GetCommaPartionUserIDs(CurrentSalesMan));                     

错误提示:Conversion failed when converting the nvarchar value '1,2,3,4,5,6,9,12,13,15,26,27,28,29' to data type int.

 

 

可以运行的语句: strSql.Append(" AND cu.CurrentSalesMan in (" + GetCommaPartionUserIDs(CurrentSalesMan) + ")");                    

 

 

 

cu.CurrentSalesMan  是 Int 类型.   

 GetCommaPartionUserIDs(CurrentSalesMan)的结果是:"1,2,3,4,5,6,9,12,13,15,26,27,28,29"

 

我的问题是为什么参数化的那条语句不能运行.而拼接的语句能运行呢?

 

 

我测试的结果是.:"1,2,3,4,5,6,9,12,13,15,26,27,28,29"是不是被参数化成了数据库中的  ('1,2,3,4,5,6,9,12,13,15,26,27,28,29') 而不是我们想要的: (1,2,3,4,5,6,9,12,13,15,26,27,28,29)

 

数据库中当CurrentSalesMan 是nvarchar时候.

use GoldHawk;

select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in (1); select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in ('1');结果相同

 

数据库中当CurrentSalesMan 是int时候.

use GoldHawk;

select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in (1); select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in ('1');结果相同

结果也相同

 

但是

use GoldHawk;

select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in (1,2); select COUNT(1) from dbo.EOS_Customers where CurrentSalesMan in ('1,2');

下边的语句会报错..

说明数据库在用 where In 的时候,会试图把in后边被逗号分割的每个元素转换成in前面字段的类型.如果转换成功.则正常运行.

转换失败则会报转换失败的错误.如 :消息 245，级别 16，状态 1，第 1 行 在将 varchar 值 '1,2' 转换成数据类型 int 时失败。