這個存儲過程主要是有兩點：

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();    }