Copy Code code as follows:
CREATE PROCEDURE [dbo]. [Up_pager]
@table varchar (2000),--table name
@col varchar (50),--Paging by this column
@orderby bit,--sort, 0-order, 1-Descending
@collist varchar,--The list of fields to query, * represents all fields
@pagesize int,--Number of records per page
@page int,--Specify page
@condition varchar (800)--Query conditions
As
DECLARE @sql nvarchar (4000), @where1 varchar (a), @where2 varchar (800),
@total_Item int, @total_Page int
IF @condition is null or RTRIM (@condition) = '
begin--No query criteria
SET @where1 = ' WHERE '
SET @where2 = '
End
ELSE
begin--have query conditions
SET @where1 = ' WHERE (' + @condition + ') and '--the condition is added to this condition
SET @where2 = ' WHERE (' + @condition + ') '--there is no condition to add this condition
End
SET @sql = ' SELECT @total_Item =ceiling ((COUNT (*) +0.0) ' + ') from ' + @table + @where2
EXEC sp_executesql @sql, N ' @total_Item int OUTPUT ', @total_Item output--calculate total number of bars
Set @total_Page = Ceiling ((@total_Item +0.0)/@pagesize)--Total number of pages calculated
IF @orderby =0
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + ' + @collist +
', ' + CAST (@total_Item as varchar) + ' as Total_item ' +
', ' +cast (@total_Page as varchar) + ' as Total_page ' +
' From mailto: ' + @table + @where1 + @col + '%3e (SELECT MAX (' + @col + ') ' +
' From (the SELECT top ' +cast (@pagesize * (@page-1) as varchar) + ' +
@col + "from" + @table + @where2 + ' ORDER BY ' + @col + ') T "ORDER by" + @col
ELSE
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + ' + @collist +
', ' + CAST (@total_Item as varchar) + ' as Total_item ' +
', ' +cast (@total_Page as varchar) + ' as Total_page ' +
' From mailto: ' + @table + @where1 + @col + '%3c (select MIN (' + @col + ') ' +
' From (the SELECT top ' +cast (@pagesize * (@page-1) as varchar) + ' +
@col + "from" + @table + @where2 + ' ORDER BY ' + @col + ' DESC) T "ORDER by ' +
@col + ' DESC '
IF @page =1--First page
SET @sql = ' SELECT top ' +cast (@pagesize as varchar) + ' + @collist +
', ' + CAST (@total_Item as varchar) + ' as Total_item ' +
', ' +cast (@total_Page as varchar) + ' as Total_page ' +
' From ' + @table +
@where2 + ' ORDER BY ' + @col +case @orderby when 0 THEN ' ELSE ' DESC ' end
--print @sql
EXEC (@sql)
Test in SQL (teach you how to use)
Copy Code code as follows:
EXEC Up_pager ' (SELECT * from table name) AA ', ' to sort column name ', 0-order or 1-reverse, ' show column ', per page record number, specify page, ' condition '
EXEC Up_pager ' (SELECT * from t_gather_page) AA ', ' Savetime ', 1, ' * ', 40, 3, '