Copy codeThe Code is as follows:
-- Based on MAX (MIN) ID
Create proc [dbo]. [proc_select_id]
@ Pageindex int = 1, -- current page number
@ Pagesize int = 10, -- size of each page
@ Tablename VARCHAR (50) = '', -- table name
@ Fields VARCHAR (1000) = '', -- query field set
@ Keyid VARCHAR (50) = '', -- primary key
@ Condition NVARCHAR (1000) = '', -- Query condition
@ Orderstr VARCHAR (500), -- sorting Condition
@ TotalRecord bigint output -- total number of records
AS
If isnull (@ orderstr, n') = n' SET @ orderstr = n' ORDER by' + @ keyid + N' des'
If isnull (@ fields, n'') = n' SET @ fields = n '*'
If isnull (@ condition, n') = n' SET @ condition = n' 1 = 1'
DECLARE @ SQL NVARCHAR (4000)
-- IF (@ totalRecord is null)
-- BEGIN
SET @ SQL = n' SELECT @ totalRecord = COUNT (*)'
+ N' from' + @ tablename
+ N 'where' + @ condition
EXEC sp_executesql @ SQL, n' @ totalRecord INT output', @ totalRecord OUTPUT
-- END
IF (@ pageindex = 1)
BEGIN
SET @ SQL = n' SELECT TOP '+ STR (@ pagesize) + n'' + @ fields + n'from' + @ tablename + n'where' + @ condition + n'' + @ orderstr
EXEC (@ SQL)
END
ELSE
BEGIN
DECLARE @ operatestr CHAR (3), @ comparestr CHAR (1)
SET @ operatestr = 'Max'
SET @ comparestr = '>'
IF (@ orderstr <> '')
BEGIN
IF (CHARINDEX ('desc', LOWER (@ orderstr) <> 0)
BEGIN
SET @ operatestr = 'Min'
SET @ comparestr = '<'
END
END
SET @ SQL = n' SELECT top '+ STR (@ pagesize) + N' + @ fields + N' from' + @ tablename + N' where' + @ keyid + @ comparestr
+ N' (SELECT '+ @ operatestr + N' (' + @ keyid + N') FROM '+ @ tablename + N' WHERE' + @ keyid
+ N' IN (select top '+ STR (@ pageindex-1) * @ pagesize) + N' + @ keyid + N' FROM' + @ tablename + N' WHERE'
+ @ Condition + N' + @ orderstr + N') AND '+ @ condition + N' + @ orderstr
EXEC (@ SQL)
END
GO
-- Based on ROW_NUMBER () OVER
Create proc [dbo]. [proc_select_page_row]
@ Pageindex INT = 1, -- current page number
@ Pagesize INT = 10, -- size of each page
@ Tablename VARCHAR (50) = '', -- table name
@ Fields VARCHAR (1000) = '*', -- query field set
@ Keyid VARCHAR (50) = '', -- primary key
@ Condition NVARCHAR (1000) = '', -- Query condition
@ Orderstr VARCHAR (500), -- sorting Condition
@ TotalRecord bigint output -- total number of records
AS
If isnull (@ orderstr, n') = n' SET @ orderstr = n' ORDER by' + @ keyid + N' des'
If isnull (@ fields, n'') = n' SET @ fields = n '*'
If isnull (@ condition, n') = n' SET @ condition = n' 1 = 1'
DECLARE @ SQL NVARCHAR (4000)
-- IF @ totalRecord IS NULL
-- BEGIN
SET @ SQL = n' SELECT @ totalRecord = COUNT (*)'
+ N' from' + @ tablename
+ N 'where' + @ condition
EXEC sp_executesql @ SQL, n' @ totalRecord bigint output', @ totalRecord OUTPUT
-- END
IF (@ pageindex = 1)
BEGIN
SET @ SQL = n' SELECT TOP '+ STR (@ pagesize) + n'' + @ fields + n'from' + @ tablename + n'where' + @ condition + n'' + @ orderstr
EXEC (@ SQL)
END
ELSE
BEGIN
DECLARE @ StartRecord INT
SET @ StartRecord = (@ pageindex-1) * @ pagesize + 1
SET @ SQL = n' SELECT * FROM (SELECT ROW_NUMBER () OVER ('+ @ orderstr + N') AS rowId, '+ @ fields + N' from' + @ tablename + N') as t where rowId> =' + STR (@ StartRecord) + N' and rowId <= '+ STR (@ StartRecord + @ pagesize-1)
EXEC (@ SQL)
END
GO
-- Based on the TOP ID
Create proc [dbo]. [proc_select_page_top]
@ Pageindex INT = 1, -- current page number
@ Pagesize INT = 10, -- size of each page
@ Tablename VARCHAR (50) = '', -- table name
@ Fields VARCHAR (1000) = '', -- query field set
@ Keyid VARCHAR (50) = '', -- primary key
@ Condition NVARCHAR (1000) = '', -- Query condition
@ Orderstr VARCHAR (500), -- sorting Condition
@ TotalRecord bigint output -- total number of records
AS
If isnull (@ orderstr, n') = n' SET @ orderstr = n' ORDER by' + @ keyid + N' des'
If isnull (@ fields, n'') = n' SET @ fields = n '*'
If isnull (@ condition, n') = n' SET @ condition = n' 1 = 1'
DECLARE @ SQL NVARCHAR (4000)
-- IF (@ totalRecord is null)
-- BEGIN
SET @ SQL = n' SELECT @ totalRecord = COUNT (*)'
+ N' from' + @ tablename
+ N 'where' + @ condition
EXEC sp_executesql @ SQL, n' @ totalRecord INT output', @ totalRecord OUTPUT
-- END
IF (@ pageindex = 1)
BEGIN
SET @ SQL = n' SELECT TOP '+ STR (@ pagesize) + n'' + @ fields + n'from' + @ tablename + n'where' + @ condition + n'' + @ orderstr
EXEC (@ SQL)
END
ELSE
BEGIN
SET @ SQL = n' SELECT TOP '+ STR (@ pagesize) + N' + @ fields + N' FROM' + @ tablename + N' WHERE '+ @ keyid
+ N' not in (select top '+ STR (@ pageindex-1) * @ pagesize) + N' + @ keyid + N' FROM'
+ @ Tablename + N 'where' + @ condition + N' + @ orderstr + N') AND '+ @ condition + N' + @ orderstr
EXEC (@ SQL)
END
GO