Stored Procedures with search criteria paging and stored procedures with search criteria

Stored Procedures with search criteria paging and stored procedures with search criteria

This storage process mainly involves two aspects:

1. dynamically splice the paging of query Conditions

2. Dynamically Retrieve the total number of rows for the current search Condition

Note: If you use this join query condition to pass in, you must first filter the special strings of the form to make the system more secure.

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) 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

The format of the background merging parameters must be noted: field name = 'value', condition such as OrderID = '000000' and Contacts = '000000'

/// <Summary> /// concatenate SQL query conditions /// </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 (strOrderStatu S! = "") {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 ();}