Today we have set up a paging storage process with good scalability. If you need it, please refer to it.
Today we have set up a paging storage process with good scalability. If you need it, please refer to it.
| The Code is as follows: |
|
USE [a6756475746]
GO
/***** Object: StoredProcedure [dbo]. [tbl_order_SearchWhereAndPage] Script Date: 11/01/2011 09:37:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter procedure [dbo]. [tbl_order_SearchWhereAndPage]
@ AllCount int OUTPUT,
@ PageIndex int,
@ PageSize int,
@ MinDate datetime,
@ MaxDate datetime
AS
Begin
DECLARE @ PageLower int
Set @ PageLower = @ PageSize * @ PageIndex
DECLARE @ PageUpper int
Set @ PageUpper = @ PageLower + @ PageSize-1 DECLARE @ SearchSQL nvarchar (4000)
Set @ SearchSQL = 'select *, (ROW_NUMBER () OVER (order by [ID] DESC)-1) AS RowNumber FROM tbl_order WHERE (1 = 1 )'
DECLARE @ SearchSQLCount nvarchar (4000)
Set @ SearchSQLCount = 'select @ count = Count (*) FROM tbl_order WHERE (1 = 1 )'
Declare @ Result [varchar] (5000)
Set @ Result =''
If @ minDate> convert (datetime, '2017-1-2 ')
Begin
Set @ Result = @ Result + 'and oDeliveryDate> = ''' + convert (varchar (20), @ minDate) + ''''
End
If @ maxDate> convert (datetime, '2017-1-2 ')
Begin
Set @ Result = @ Result + 'and oDeliveryDate <= ''' + convert (varchar (20), dateadd (dd, 1, @ maxDate) + ''''
End
Set @ SearchSQLCount = @ SearchSQLCount + @ Result
Set @ SearchSQL = @ SearchSQL + @ Result SET @ SearchSQL = 'WITH t AS (' + @ SearchSQL + ')
SELECT * FROM t
WHERE [RowNumber] between' + convert (varchar (50), @ PageLower) + 'AND' + convert (varchar (50), @ PageUpper) +'
Order by RowNumber' Exec (@ SearchSQL) Exec sp_executesql @ SearchSQLCount, n' @ count as int out', @ AllCount out Print @ SearchSQL
Print @ AllCount
End
Exec (@ Result) |
