sql server 'IN' 拼接SQL 在C# 中匹配问题

发布时间 2023-09-20 15:06:18作者: lixia64
var sql = @"
                    select distinct a.Empno, a.Alarmdate,l.Wdat,l.Empno,l.Empnm,l.Depno,l.Depnm
                    ,l.Clsno,l.Time1,l.Time2,l.Wtime1,l.Wtime2
                    ,l.Latet,l.Erat,l.Offtime,l.Memo

                    from 数据库.dbo.名称 a
                    left join 数据库.dbo.名称 l 
                    on a.Empno = l.Empno and a.Alarmdate = l.Wdat
                    where 1=1 ";

                List<SqlParameter> pars = new List<SqlParameter>();

                var Authority = HasHrAuthority(model.loginEmpno);

                var empList = string.Empty;
                //var empList = new List<string>();
                if (Authority.Contains("N"))//非最高级权限
                {
                    empList = HasAssistantAuthorityForString(model.loginEmpno);

                    if (string.IsNullOrEmpty(empList))
                    {
                        sql += "and a.Empno = " + model.empNo + ""; //普通用户
                    }
                    else
                    {
                        sql += "and a.Empno in ( " + empList + ") ";     //管理者权限  √这样写没问题
                     
              //sql += "and a.Empno in ( @EmpNo ) ";   这样写查不出资料  
              //
pars.add(new sqlparameter("@EmpNo",empList))
 } } 

if (!string.IsNullOrEmpty(model.startAlarmDate))
{ sql
+= "and a.Alarmdate >=" + "'" + model.startAlarmDate + "'" + " "; }

if (!string.IsNullOrEmpty(model.endAlarmDate))
{ sql
+= "and a.Alarmdate <= " +"'"+ model.endAlarmDate + "'" + " "; }


DataSet dt
= SqlHelper.Query(sql, "AttendanceTable");

emplist 为list <string> 循环拼接成 string 类型 时 每个id需要加上 '    '

 eg: result += " ' " + data["empno"].ToString() + " ' " + ",";