.net sql where in 参数化

其实要执行的东西很简单就是一句.(ps,随手敲的,不排除手误)

select * from tb where id in(1,2,3,4)

那么就成了语句的构造问题了

in ()里面是个集合,而楼主的写法是 id in ('1,2,4,3') 是in一个字串. 当然取不到了.
string sql="select * from tb where id in(@ids)";
相当于
string sql="select * from tb wehre id in('1,2,3,4');

如果一定要这样以传参数方式,并且ids给字串,那么

C# code

string sql = " exec('select * from tb where id in (' + @ids + ')') " SqlParameter[] Para1 = new SqlParameter[ 1 ]; Para1[ 0 ] = new SqlParameter( " @id " , SqlDbType.NVarChar, 200 ); Para1[ 0 ].Value = idStr; return SqlHelper.ExecuteDataset(My_config.GetConnstr, CommandType.Text, sql, Para1);

相当于执行

SQL code

exec ( ' select * from tb where id in ( ' + ' 1,2,3,4 ' + ' ) ' )

如果ids给字串,但不要求以传参数方式执行 那么

C# code

string sql = " select * from tb wehre id in ( " + idStr + " ) " ; return SqlHelper.ExecuteDataset(My_config.GetConnstr, CommandType.Text, sql, null );

相当于执行

SQL code

select * from tb where id in ( 1 , 2 , 3 , 4 )

如果一定要这样以传参数方式,且ids给字串,且不允许语句用exec来执行,那么

C# code

string sql = " select * from tb where charindex(',' + rtrim(id) + ',' , ',' + @ids + ',')>0 " ; SqlParameter[] Para1 = new SqlParameter[ 1 ]; Para1[ 0 ] = new SqlParameter( " @id " , SqlDbType.NVarChar, 200 ); Para1[ 0 ].Value = idStr; return SqlHelper.ExecuteDataset(My_config.GetConnstr, CommandType.Text, sql, Para1);

相当于执行

SQL code

select * from tb where charindex ( ' , ' + rtrim (id) + ' , ' , ' , ' + ' 1,2,3,4 ' + ' , ' ) > 0

当然,charindex方式可以改用like完成.写法略

转载于:https://www.cnblogs.com/plin2008/archive/2009/08/10/1542721.html