带搜索条件分页的存储过程

这个存储过程主要是有两点：

1、动态拼接查询条件的分页

2、动态获取当前搜索条件的总行数

注意：如果用这种拼接查询条件传进来，为了更加地让系统安全，需要先过滤表单的特殊字符串

ALTER PROCEDURE [dbo].[OrdersListByCondition]     @pagesize int,  @pageindex int,  @condition nvarchar(1000),  @totalcount int outputASBEGINdeclare @sql nvarchar(3000)declare @countSql nvarchar(3000)if @condition=''begin--需要什么条件自己将*替换掉set @sql=' select * from  (select top  '+cast(@pagesize as nvarchar(30)) +' * from  E_Orders  where OrderID not in (select top ('+cast(@pagesize as nvarchar(30)) +' *('+cast(@pageindex as nvarchar(30)) +'-1)) OrderID from E_Orders  order by OrderID desc) order by OrderID desc) o left join E_OrdersCommodity oc on o.OrderID=oc.OrderID  order by OrderID desc'set @countSql='select @a=count(1) from E_Orders'endelsebegin set @sql=' select * from  (select top  '+cast(@pagesize as nvarchar(30)) +' * from  E_Orders  where OrderID not in (select top ('+cast(@pagesize as nvarchar(30)) +' *('+cast(@pageindex as nvarchar(30)) +'-1)) OrderID from E_Orders where '+ @condition+' order by OrderID desc) and '+ @condition+' order by OrderID desc) o left join E_OrdersCommodity oc on o.OrderID=oc.OrderID  order by OrderID desc'set @countSql='select @a=count(1) from E_Orders where '+ @condition+''endexec(@sql)execute sp_executesql @countSql,N'@a INT output',@totalcount outputEND

后台拼接参数需要注意格式 ：字段名='值'，条件如 OrderID='20150721578942' and Contacts='小四'

 /// <summary>    /// 拼接SQL查询条件    /// </summary>    /// <param name="strOrderStatus"></param>    /// <param name="strOrderID"></param>    /// <param name="strMallName"></param>    /// <param name="strContacts"></param>    /// <returns></returns>    private string[] GetConditionArray(string strOrderStatus,  string strOrderID, string strMallName, string strContacts)    {        List<string> list = new List<string>();        if (strOrderStatus != "")        {            list.Add("OrderStatus='" + strOrderStatus + "'");        }if (strOrderID != "")        {            list.Add("OrderID='" + strOrderID + "'");        }        if (strMallName != "")        {            list.Add("MallName='" + strMallName + "'");        }        if (strContacts != "")        {            list.Add("Contacts='" + strContacts + "'");        }        return list.ToArray();    }