Complete example of MSSQL paging Stored Procedure (supporting multi-Table paging storage), mssql Stored Procedure

Complete example of MSSQL paging Stored Procedure (supporting multi-Table paging storage), mssql Stored Procedure

This example describes the paging Stored Procedure of MSSQL. We will share this with you for your reference. The details are as follows:

USE [DB_Common] GO/***** object: StoredProcedure [dbo]. [Com_Pagination] script Date: 03/09/2012 23:46:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO /************************* * ************************************ SQL paging Storage process (supports multi-Table paging storage) ** call instance: EXEC Com_Pagination 100, -- total number of records 0, -- total number of pages -- 'person ', -- Query table name 'person p left join te a ON. PID = p. id', -- Name of the queried table (multiple tables here) 'A. * ', -- query the data column 'p. id', -- arrange the field 'p. id', -- Group Field 2, -- the number of records per page 1, -- the current page number is 0, -- whether to use the group, whether it is 'a. pid = 2' -- Query condition ********************************* * *************************/create procedure [dbo]. [Com_Pagination] @ TotalCount int output, -- total number of records @ TotalPage int output, -- total number of pages @ Table NVARCHAR (1000), -- Name of the Table to be queried (multiple tables are allowed, for example: person p left join te a ON. PID = p. id) @ Column NVARCHAR (1000), -- query field, which can be multiple columns or * @ OrderColumn NVARCHAR (100), -- Sort field @ GroupColumn NVARCHAR (150 ), -- group field @ P AgeSize INT, -- number of records per page @ CurrentPage INT, -- current page number @ Group TINYINT, -- whether to use Group, whether it is @ Condition NVARCHAR (4000) -- Query Condition (note: if you want to query multiple tables at this time, you can also follow the condition here, for example,. pid = 2) ASDECLARE @ PageCount INT, -- total number of pages @ strSql NVARCHAR (4000), -- main query statement @ strTemp NVARCHAR (2000), -- temporary variable @ strCount NVARCHAR (1000 ), -- Statistical Statement @ strOrderType NVARCHAR (1000) -- Sort statement BEGINSET @ PageCount = @ PageSize * (@ CurrentPage-1) SET @ strOrderType = 'ORDER BY' + @ OrderColumn + ''If @ Condition! = ''In in IF @ CurrentPage = 1 begin if @ GROUP = 1 begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'where' + @ Condition + 'group by' + @ GroupColumn SET @ strCount = @ strCount + 'set @ TotalCount = @ ROWCOUNT' SET @ strSql = 'select top' + STR (@ PageSize) + ''+ @ Column + 'from' + @ Table + 'where' + @ Condition + 'group by' + @ GroupColumn +'' + @ strOrderType end else begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'where' + @ Condition SET @ strSql = 'select top' + STR (@ PageSize) + ''+ @ Column + 'from' + @ Table + 'where' + @ Condition +'' + @ strOrderType end else begin if @ GROUP = 1 begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'where' + @ Condition + 'group by' + @ GroupColumn SET @ strCount = @ strCount + 'set @ TotalCount = @ ROWCOUNT' SET @ strSql = 'select * FROM (select top (2000) '+ @ Column +', ROW_NUMBER () OVER ('+ @ strOrderType + ') as num from '+ @ Table + 'where' + @ Condition + 'group by' + @ GroupColumn +') as t where num between '+ STR (@ PageCount + 1) + 'and' + STR (@ PageCount + @ PageSize) end else begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'where' + @ Condition SET @ strSql = 'select * FROM (select top (2000)' + @ Column + ', ROW_NUMBER () OVER ('+ @ strOrderType +') as num from '+ @ Table + 'where' + @ Condition +') as t where num between '+ STR (@ PageCount + 1) + 'and' + STR (@ PageCount + @ PageSize) end endendelse -- no query condition begin if @ CurrentPage = 1 begin if @ GROUP = 1 begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'group by' + @ GroupColumn SET @ strCount = @ strCount + 'set @ TotalCount = @ ROWCOUNT' SET @ strSql = 'select TOP '+ STR (@ PageSize) + ''+ @ Column + 'from' + @ Table + 'group by' + @ GroupColumn +'' + @ strOrderType end else begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table SET @ strSql = 'select TOP' + STR (@ PageSize) + ''+ @ Column + 'from' + @ Table +'' + @ strOrderType end else begin if @ GROUP = 1 begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table + 'group by' + @ GroupColumn SET @ strCount = @ strCount + 'set @ TotalCount = @ ROWCOUNT' SET @ strSql = 'select * FROM (SELECT TOP (2000) '+ @ Column +', ROW_NUMBER () OVER ('+ @ strOrderType +') as num from '+ @ Table + 'group by' + @ GroupColumn + ') as t where num between '+ STR (@ PageCount + 1) + 'and' + STR (@ PageCount + @ PageSize) end else begin set @ strCount = 'select @ TotalCount = COUNT (*) FROM '+ @ Table SET @ strSql = 'select * FROM (select top (2000) '+ @ Column +', ROW_NUMBER () OVER ('+ @ strOrderType +') as num from '+ @ Table + ') as t where num between '+ STR (@ PageCount + 1) + 'and' + STR (@ PageCount + @ PageSize) end endendexec sp_executesql @ strCount, n' @ TotalCount INT output ', @ TotalCount OUTPUTIF @ TotalCount> 2000 begin set @ TotalCount = 2000 ENDIF @ TotalCount % @ PageSize = 0 begin set @ TotalPage = @ TotalCount/@ PageSizeENDELSEBEGIN SET @ TotalPage = @ TotalCount/@ PageSize + 1 endset nocount onexec (@ strSql) endset nocount off/** call instance: EXEC Com_Pagination 100, -- total number of records 0, -- total number of pages -- 'person ', -- Name of the queried table 'person p left join te a ON. PID = p. id', -- Name of the queried table (multiple tables here) 'A. * ', -- query the data column 'p. id', -- arrange the field 'p. id', -- group field 2, -- number of records per page 1, -- current page 0, -- whether to use group, or not 'a. pid = 2' -- Query condition SELECT. * FROM Person p left join te a ON. PID = p. idWHERE. pid = 2 **/

I hope this article will help you design SQL Server database programs.