SQL paging Stored Procedure Code and SQL paging Stored Procedure
The Stored Procedure Code of SQL paging can be directly used by friends, which is very easy to use.
The sharing code is as follows:
USE [SendMessage] GO/****** Object: StoredProcedure [dbo]. [pages] Script Date: 07/09/2015 13:46:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongoalter proc [dbo]. [pages] (@ tbname nvarchar (100), -- Name of the table to be displayed by PAGE @ FieldKey nvarchar (1000), -- used to locate the record's primary key (unique key) field, multiple Fields separated by commas @ PageCurrent int = 1, -- the page number to be displayed @ PageSize int = 10, -- the size of each page (number of records) @ FieldShow nvarchar (1000) = '', -- List of fields to be displayed separated by commas (,). If not specified, all fields are displayed @ FieldOrder nvarchar (1000) = '', -- List of sorting fields separated by commas (,). You can specify DESC/ASC @ WhereString nvarchar (1000) = n' After the field -- Query condition) ASbegin if isnull (@ FieldKey, n'') = ''begin RAISERROR (n'primary key (or unique key) ',) returnend if isnull (@ PageCurrent, 0) <1 SET @ PageCurrent = 1 if isnull (@ PageSize, 0) <1 SET @ PageSize = 10 if isnull (@ FieldShow, n '') = n' SET @ FieldShow = n' * 'if isnull (@ FieldOrder, n '') = n'set @ FieldOrder = n' else set @ FieldOrder = n' ORDER by' + LTRIM (@ FieldOrder) if isnull (@ WhereString, n '') = n''set @ WhereString = n''else SET @ WhereString = n'where' + @ WhereString + n'' -- calculate the TOPN value DECLARE @ TopN varchar (20 ), @ StartRecord varchar (20), @ EndRecord varchar (20) SELECT @ TopN = @ PageSize, @ StartRecord = (@ PageCurrent-1) * @ PageSize + 1, @ EndRecord = (@ PageCurrent-1) * @ PageSize + @ PageSize -- IF @ PageCurrent = 1 EXEC (N 'select top' + @ TopN + N' + @ FieldShow + N 'from' + @ tbname + N' + @ WhereString + N' + @ FieldOrder) ELSEEXEC (n' with temptbl as (select ROW_NUMBER () Over ('+ @ FieldOrder +') as row, '+ @ FieldKey + 'from' + @ tbname + N' + @ WhereString +') select' + @ FieldShow + 'from (select B. * from (select '+ @ FieldKey +' from temptbl where row between '+ @ StartRecord +' and '+ @ EndRecord + ') A left join '+ @ tbname +' B on. '+ @ FieldKey +' = B. '+ @ FieldKey +') C') END
The above is all the content of this article, hoping to help you learn.