Three paging Stored Procedure Codes in mmssqlserver

This article introduces the three paging Stored Procedure Implementation codes in mmssqlserver. If you have any need, refer to the example below. Let's take a look.

This article introduces the three paging Stored Procedure Implementation codes in mmssqlserver. If you have any need, refer to the example below. Let's take a look.

-- Based on MAX (MIN) ID

The Code is as follows:

Create proc [dbo]. [proc _ 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

The Code is as follows:

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

The Code is as follows:

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