存储过程执行无数据 无报错

[hebaobao19880921]

在CSDN各位前辈的指引下 成功拼接多条件查询 但是为了锻炼自己的存储过程熟练度 我写了一段存储过程来实现
执行却没有数据 也不报错 帮忙看看
CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null,
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS
BEGIN
select * from [t_File]
where FileNam like case when @FileNam is null then FileNam else @FileNam end
and Decription like case when @Decription is null then Decription else @Decription end
and (@stardate is null or CreateOn>=@stardate) and (@enddate is null or CreateOn<=@enddate)
END

这个存储过程很简单 是一个查询 不过其中有一个日期段 开始日期和结束日期.

SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@FileNam", model.FileNam);
            parameters[1] = new SqlParameter("@Decription", model.Decription);
            parameters[2] = new SqlParameter("@stardate", createdate);
            parameters[3] = new SqlParameter("@enddate", endate);
            return DbHelperSQL.RunProcedure("P_UserSerch", parameters, "t_File");

/// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

[风骑士之怒]

打开事件探查器，查看执行的具体SQL，然后就应该明白哪里出错了。可以贴出来，我帮你改

[hebaobao19880921]

[Quote=引用 1 楼 wknight_it 的回复:]
打开事件探查器，查看执行的具体SQL，然后就应该明白哪里出错了。可以贴出来，我帮你改
[/Quote]

 string strSql = "select * from t_File where 1 =1";
            string strWhere = "";
            if (!string.IsNullOrEmpty(model.FileNam))
            {
                strWhere += " and FileNam like @FileNam";
            }
            if (!string.IsNullOrEmpty(model.Decription))
            {
                strWhere += " and Decription like @Decription";
            }
            if (!string.IsNullOrEmpty(createdate) || !string.IsNullOrEmpty(endate))
            {
                strWhere += " and CreateOn between @createdate and @endate";
            }
            strSql += strWhere;
            SqlParameter[] parameters = {
                new SqlParameter("@FileNam",SqlDbType.NVarChar,256),
                new SqlParameter("@Decription",SqlDbType.NVarChar,256),
                new SqlParameter("@createdate",SqlDbType.NVarChar),
                new SqlParameter("@endate",SqlDbType.NVarChar)
            };
            parameters[0].Value = "%" + model.FileNam + "%";
            parameters[1].Value = "%" + model.Decription + "%";
            parameters[2].Value = createdate;
            parameters[3].Value = endate;
            return DbHelperSQL.Query(strSql, parameters);

这是原句

[暖枫无敌]

parameters[0].Value = "'%" + model.FileNam + "%'";
parameters[1].Value = "'%" + model.Decription + "%'";

[hebaobao19880921]

[Quote=引用 3 楼 taomanman 的回复:]
C# code

parameters[0].Value = "'%" + model.FileNam + "%'";
parameters[1].Value = "'%" + model.Decription + "%'";
[/Quote]

意思是我这里错了?

[挨踢直男]

你想把你二楼发的代码转换成存储过程

[hebaobao19880921]

[Quote=引用 5 楼 aspwebchh 的回复:]
你想把你二楼发的代码转换成存储过程
[/Quote]
嗯 是的 转换中出了点问题

[hebaobao19880921]

[Quote=引用 3 楼 taomanman 的回复:]
C# code

parameters[0].Value = "'%" + model.FileNam + "%'";
parameters[1].Value = "'%" + model.Decription + "%'";
[/Quote]
呵呵 原来你误解我的意思了 我不是说2楼的查不出来 2楼得是利用SQL拼接的多条件查询 可以查询到数据 是成功的 我想把2楼的代码改成存储过程提高一下自己的存储过程熟练度 但是查询不出来

[早起晚睡]

SELECT @FileNam ='%'+ @FileNam + '%'
SELECT @Decription='%'+ @Decription+ '%'

在存储过程中加入这两句

[早起晚睡]

CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null,  
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS  
BEGIN  
select * from [t_File]  
where FileNam like case when @FileNam is null then FileNam else '%'+@FileNam+'%' end  
and Decription like case when @Decription is null then Decription else '%'+@Decription+'%' end
and (@stardate is null or CreateOn>=@stardate) and (@enddate is null or CreateOn<=@enddate)
END  

[pengwen420107]

单引号问题？

[挨踢直男]

CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null, 
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS 
BEGIN 

declare @sql nvarchar(4000)
set @sql = 'select * from t_file where 1= 1'
if len(@filenam) >0
   set @sql = @sql + ' and fileNam like '''+@FileNam+''''

if len(@decription)>0
   set @sql = @sql +' and decription like '''+@decription+''''

if len(@startdate)>0 or len(@enddate)>0)
   set @sql = @sql +' and createOn between '''+@startdate +''' and '''+@enddate+''''

exec @sql

END  

[挨踢直男]

CREATE PROCEDURE P_UserSerch
@FileNam varchar(20) = null, 
@Decription varchar(20)=null,
@stardate varchar(20) =null,
@enddate varchar(20) =null
AS 
BEGIN 

declare @sql nvarchar(4000)
set @sql = 'select * from t_file where 1= 1'
if len(@filenam) >0
   set @sql = @sql + ' and fileNam like '''+@FileNam+''''

if len(@decription)>0
   set @sql = @sql +' and decription like '''+@decription+''''

if len(@startdate)>0 or len(@enddate)>0
   set @sql = @sql +' and createOn between '''+@startdate +''' and '''+@enddate+''''

exec @sql

END  

[风骑士之怒]

[Quote=引用 2 楼 hebaobao19880921 的回复:]
引用 1 楼 wknight_it 的回复:
打开事件探查器，查看执行的具体SQL，然后就应该明白哪里出错了。可以贴出来，我帮你改



C# code

string strSql = "select * from t_File where 1 =1";
string strWhere = "";
if (!string.IsN……
[/Quote]

我要看的是你执行的SQL语句，错误应该是少了百分号，你对照你下你成功执行的语句 和 存储过程，看看有什么不同。