sql 条件选择 显示列表 问题

[kehua668]

c# 代码没问题，显示正常，但现在我要把这段select写到存储过程里，就有困难了，

 public static IList<QhModel> Get_fapiao(String StartTime, String EndTime, String djlx, String dq,String name)
        {
            string sql = "select * from hr_qhcjlis where (jabz='1') and ((qia='" + name + "') or (td='" + name + "') or (djry='" + name + "')) and (cwja='0') and ((right(shzt,1)<>2)) and ";
            string sort = "order by djrq desc , qhid desc ";
            if (StartTime != "" && EndTime != "" && StartTime != null && EndTime != null)
                sql += "  (djrq between '" + StartTime + "' and '" + EndTime + "')";
            if (djlx != "" && djlx != null)
                sql += " and (djlx like '%" + djlx + "%')";
            if (dq != "" && dq != null)
                sql += " and (dq like '%" + dq + "%')";
            return Get_CallBySql(sql + sort);
        }
 public static IList<QhModel> Get_CallBySql(string safeSQL)
        {
            List<QhModel> list = new List<QhModel>();
            try
            {
                using (SqlDataReader rec = DBHelper2.GetReader(safeSQL))
                {
                    if (rec.HasRows)
                    {
                        while (rec.Read())
                        {
                            QhModel qhmodel = new QhModel();
                            //qhmodel.qhid = Int32.Parse(rec["id"].ToString());
                            qhmodel.djh = rec["djh"].ToString();
                            qhmodel.djrq = DateTime.Parse(rec["djrq"].ToString());
                            qhmodel.djry = rec["djry"].ToString();
                            qhmodel.qhlist = rec["qhlist"].ToString();
                            qhmodel.djlx = rec["djlx"].ToString();
                            qhmodel.shzt = rec["shzt"].ToString();
                            qhmodel.dq = rec["dq"].ToString();
                            qhmodel.cwfph = rec["cwfph"].ToString();
                            list.Add(qhmodel);
                        }
                    }
                    rec.Close();
                    return list;
                }
                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw ex;
            }
        }

以下是我的sql

CREATE PROCEDURE  p_cxfp 
@name varchar(50),
@startime datetime,
@endtime datetime,
@djlx  varchar(50),
@dq varchar(50)

 AS
begin
declare @str  varchar(500)
select @str=''
declare @str1  varchar(500)
select @str1=''

if  (@startime != null  and  @endtime != null)
set @str1=@str1+' and (djrq between @startime and @endtime) '

if @djlx != null
set @str1=@str1+' and (djlx like "%+ @djlx +%") '

 if @dq != null
set @str1=@str1+' and (dq like "% + @dq+  %") '   

set  @str=' select * from hr_qhcjlis  where (jabz=1) and (cwja=0) and  ((td="@name") or (qia="@name") or (djry="@name"))'
set  @str=@str+'and  ((td="+@name+") or (qia="+@name+") or (djry="+@name+"))'
set  @str=@str+' and charindex(''2'',shzt) != 0 '
set  @str=@str+@str1
set @str=@str+' order by djrq desc , qhid desc'

exec (@str)

end
GO

但是报错！
谁能帮我？

[wuyq11]

在查询分析器执行print @str
if(@djlx <>'') and (@djlx is not null)
begin
set @str=@str+' and djlx like ''%'+@djlx +'%'''
end

[xieluhui08]

同意二楼的说法

[kehua668]

错误提示：Must declare the variable '@startime'
但实际是有值的。

public static IList<QhModel> Get_fapiao2(string startime, string endtime, string djlx, string dq, string name)
        {
            SQLHelper hrsqlhelp = new SQLHelper();
            SqlParameter[] par = {hrsqlhelp.CreateInParam("@startime", SqlDbType.DateTime,8,startime),
                                           hrsqlhelp.CreateInParam("@endtime", SqlDbType.DateTime,8, endtime),
                                           hrsqlhelp.CreateInParam("@djlx", SqlDbType.VarChar, 50, djlx),
                                        hrsqlhelp.CreateInParam("@dq", SqlDbType.VarChar, 50, dq),
                                        hrsqlhelp.CreateInParam("@name", SqlDbType.VarChar, 50, name) };
            SqlDataReader dr = null;
            try
            {
                hrsqlhelp.RunProc("p_cxfp", par, out dr);
            }
            catch (Exception ex)
            {
                SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
            List<QhModel> list = new List<QhModel>();
               
            while (dr.Read())
            {
                QhModel qhmodel = new QhModel();
                qhmodel.djh = dr["djh"].ToString();
                qhmodel.djrq = DateTime.Parse(dr["djrq"].ToString());
                qhmodel.djry = dr["djry"].ToString();
                qhmodel.qhlist = dr["qhlist"].ToString();
                qhmodel.djlx = dr["djlx"].ToString();
                qhmodel.shzt = dr["shzt"].ToString();
                qhmodel.dq = dr["dq"].ToString();
                qhmodel.cwfph = dr["cwfph"].ToString();
                list.Add(qhmodel);
            }
            return list;
        }

[kehua668]

如果用这个存储过程，没问题！数据显示正常

CREATE PROCEDURE  p_cxfp2 
@name varchar(20),
@startime datetime,
@endtime datetime,
@djlx  varchar(50),
@dq varchar(50)

 AS
select * from hr_qhcjlis  where (jabz='1')  and ((qia= @name ) or (td=@name) or (djry=@name )) and (cwja='0') 
and (djlx like '%'+@djlx+'%') and (dq like '%'+@dq+'%')  
and (djrq between @startime and @endtime) 
order by djrq desc , qhid desc
GO

[fangxiaofelix]

帮忙顶一下，别沉了、！

[kehua668]

不沉不行啊，快帮我解决呀

[sdler]

看一下你的变量都没有给他赋值，没有值他怎么可能会显示出来