Pager procedure for SQL Server

Server SET quoted_identifier on Go SET ansi_nulls in Go ALTER procedure dbo.pager (@page_num int = 1, @item_count int = 0, @query_suffix nvarchar (4000), @query_fields nvarchar (4000) = ' * ', @query_key nvarchar (4000), @record_count int = null O UTPUT)/****************************************************************************** * * File: Pager.sql * * Name: Pager * * * * * Parameters: @page_num page number, the starting page number is 1, the default page number is 1. * * @item_count the number of record bars displayed per page, which displays 0 by default. 0 indicates that all data is removed. * * @query_suffix the query suffix, which is all statements after from. * * @query_fields fields that need to be queried, default to all fields ' * '. * * @query_key Query PRIMARY KEY * * @record_count the total number of records, when the parameter is NULL, the rollup operation is not performed. This parameter is not in effect. * * * Description: Page-Splitter. * * Remove the query for the specified interval. * * Use NOTICE: * * 1 query must explicitly specify the sort method. * * 2 Query_key must appear in the Query_fields. * * 3 Query_key should be a field that is not allowed to be empty and is not duplicated. * * Example: * * Use this function to remove * * Select a.ID, A.name, b.school_name from User A, school b where a.school_id=b.id ORDER BY a.id Desc * Data for the second page, 10 records per page * * EXEC pager @page_num = 2, @item_count = ten, @query_suffix = ' User A, school b where a.school_id=b.id O Rder by a.id desc ', @query_fields = 'a.ID, A.name, B.school_name ', @query_key = ' a.id ' * * * * * * to take out all records * * EXEC pager @query_suffix = ' User A, school b where a.school _id=b.id ORDER BY a.id desc ', @query_fields = ' a.id, a.name, B.school_name ', @query_key = ' a.id ' * * * Created: whxbb@20030108 * * Modified : * * * * * * return: Success 0 failure error code. /AS--query statement declare @query nvarchar ( 4000) declare @query_start nvarchar (4000) DECLARE @query_end varchar (4000)--Error number declare @error_code INT--Start record number declare @begin_no INT--end record number declare @end_no int set @query_suffix = ' from ' + @query_suffix if (@item_count = 0) begin set @q uery = ' SELECT ' + @query_fields + ' + @query_suffix end else if (@page_num = 1) Begin--the first page, directly using top N to take the value set @query = ' Select top ' + cast (@item_count as nvarchar) + "+ @query_fields +" + @query_suffix end else--not the first page begin-- The last record number on the previous page set @begin_no = (@page_num-1) * @item_count--The last record number of this page set @end_no = @begin_no + @item_count--Build a paging query statement s Et@query_start = ' Select top ' + cast (@end_no as nvarchar) + ' + @query_fields set @query_start = @query_start + ' fro M (' + @query_start + ' + @query_suffix + ') as query_table where ' Set @query_end = ' Select top ' + CAST (@begin_no as NV Archar + ' + @query_key + ' + @query_suffix set @query = @query_start + ' + @query_key + ' not in (' + @query_en D + ' "End print ' query constructed: ' + @query--Execute Paging query statement exec (@query) Set @error_code = @ @error if @error_code <&gt ; 0 goto Error_handle if (@record_count is not null) begin--total statistic Results--Create a temporary stored procedure to take out the result set @query = ' Create Procedur e #tmp_procedure_pager_count (@count int output) as select top percent ' + @query_key + ' + @query_suffix + ' SELECT @ count=@ @rowcount ' print ' Count query constructed: ' + @query exec (@query) Set @error_code = @ @error if @error_code <> 0 Goto Error_handle--Execute temporary stored procedure exec #tmp_procedure_pager_count @record_count output Set @error_code = @ @error if @error_ Code <> 0 Goto ERror_handle--Delete temporary stored procedure drop procedure #tmp_procedure_pager_count end Error_handle:return @error_code return @error_code Go SET QUOTED_IDENTIFIER off go to set ANSI_NULLS on Go--use example execute pager @page_num = 1, @item_count = 3324, @que Ry_suffix= '  test where 1=1 order by entity_id ', @query_key = ' entity_id ' declare @i int set @i = 0 Exec Pager @query_su ffix= ' test where 1=1 order by entity_id ', @query_fields = ' Entity_name, entity_short_name, entity_id ', @query_key = ' entity _id ', @record_count =@i output Select @i