sqlserver 存储过程 where Id in 传参

 sqlserver 存储过程 where Id in 传参

----更新多条数据，不传参
update Students set StuName='12348888' where StuID in ('1','2')

 

----更新多条数据，传参
DECLARE   @StuID   Varchar(50)   
set   @StuID='1,2'--是从参数中得到字符  '3908,3934'   
update Students set StuName='123666888'
WHERE charindex(',' + cast(StuID as varchar) + ',',',' + @StuID + ',') > 0

 

commandTextSB.Append(" DECLARE @StuID Varchar(50) set @StuID=@StuIDAAA update Students set StuName=@StuName where charindex(',' + cast(StuID as varchar) + ',',',' + @StuID + ',') > 0");

//创建命令对象
                SqlCommand cmd = new SqlCommand(commandTextSB.ToString(), myConn);
                //执行命令
                string stuIDArray = "1,2";
                cmd.Parameters.Add(new SqlParameter("@StuIDAAA", stuIDArray));           
                cmd.Parameters.Add(new SqlParameter("@StuName", "81111118"));
                int exeInt = cmd.ExecuteNonQuery();
                Console.WriteLine(exeInt);

=========================================================

这是整形Int的

结果：

sqlRtGoodsID=  1000002,1002808,1002822,1002832,1002845

string sqlRtGoodsID = @" 
                    declare 
                    @Str varchar(max)
                     set @Str=( select (','+ convert(varchar(20), goodsID)) from PercenTageRt for xml path(''))
                    select substring( @Str,2,len(@Str)) ";
                var resultGoodsIDStr = await _dbBase.QueryFirstOrDefaultAsync<string>(sqlRtGoodsID);
                if (!string.IsNullOrEmpty(resultGoodsIDStr))
                {
                    sqlPage2And += " and goodsid not in (" + resultGoodsIDStr + ") ";
                }

 

========================================================

这是字符串varchar()的

declare 
                    @Str varchar(max)
                     set @Str=( select top 10 (','''+ convert(varchar(20), FlowID)+'''') from Trade where 1=1 and UserID='4023' for xml path(''))
                    select substring( @Str,2,len(@Str))

----------------------------------------------------------------------------------

//筛选Trade中的FlowID
                string sqlTradeLike = @" declare 
                                        @Str varchar(max)
                                        set @Str=( select (','''+ convert(varchar(50), t.FlowID)+'''') from Trade t where 1=1 "
                                        + sqlPage2Where
                                        + "for xml path('')) "
                                        + "select substring( @Str,2,len(@Str))";

                var resultTradeLike = _IDbConnection.QueryFirstOrDefault<string>(sqlTradeLike, request);
                string whereIn = resultTradeLike == null ? "'0'" : resultTradeLike;

-------------------------------------------------------------------------------

 

结果是这样的：

不为null的情况：

 

'F201803271145','F201803271145','F201803271145','F201803271145','F201803271145','F201803271145','F201803271145','F201803271145','F201803271145','F201803271145'

 

为null的情况：

 

'0'

 

里面的top 10 和FlowID字段名，Trade表等，可以自由调整。

 

最后C#里执行以下语句：

 

string sqlPage2 = @"                    
                                 select Row_number() over(order by tf.confirmDate desc) as                               rownum,tf.* 
                                 from 
                                 TradeFlow tf where tf.FlowID in ( " + whereIn+")";

 

搜索

复制