SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON 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 output) / **************************************************** ************************************* ** File: Pager.sql ** Name: pager * * ** Parameters: @PAGE_NUM page, the start page code is 1, the default page number is 1. ** @item_count The number of records displayed per page is displayed by default. 0 indicates that all data is removed. ** @query_suffix query suffix, all statements behind from from. ** @query_fields requires the field of query, and defaults to all field '*'. ** @query_key query primary key ** @Record_count record total, when the parameter is NULL, no summary operation is performed. This parameter has not yet taken effect. ** ** Description: Pagers. ** Remove the query of the specified interval. ** Use Notice: ** 1 Query must explicitly specify the sort mode. ** 2 Query_Key must appear in Query_Fields. ** 3 query_key should be a field that is not allowed to be empty, and not repeated.
** Example: ** 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, 10 records per page ** exec match @Page_num = 2, @item_count = 10, @ 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' ** ** 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 '** ** creation: WHXBB @ 20030108 ** Modification: ** * * ** Return: success 0 failed error number. ************************************************* ************************************************** / 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 @query =' SELECT ' @query_fields ' ' @query_suffix end else if (@PAGE_NUM = 1) Begin - - First, use Top N Value Set @query = 'SELECT TOP' CAST (@item_count as nvarchar (10)) '' @query_fields ' @query_suffix end else - is not the first page BeGin - last record number set @begin_no = (@PAGE_NUM - 1) * @ITEM_COUNT - This page of the last record number set @end_no = @begin_no @item_count - Build Points Query Statement Set @Query_Start = 'SELECT TOP' CAST (@end_no as nvarchar (10)) '
' @Query_fields set @query_start = @query_start ' from ( ' @query_start ' ' @query_suffix ') as query_table where 'set @query_end =' select top ' cast (@begin_no as nvarchar (10)) '' @query_key ' @query_suffix set @query = @query_start ' ' @query_key ' NOT IN (' @query_end ') 'end print' query constructed: ' @query - execution Page query statement exec (@query) set @error_code = @@ error if @error_code <> 0 goto error_handle if (@Record_count is not null) Begin - The total number of statistics results - Create a temporary stored procedure to bring out the construct query the results set @query statement = 'create procedure #tmp_procedure_pager_count (@count int output) as select top 100 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 - temporary storage during execution 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 SET ANSI_NULLS ON GO - use example execute pager @page_num = 1, @item_count = 3324, @ query_suffix = 'test where 1 = 1 order by entity_id', @ query_key = 'entity_id 'Declare @i int set @i =
