Copy Code code as follows:
/*
* @curentpage Current Page
* @pagesize number of records per page
* @TableName Table name
* @key PRIMARY KEY (auto sort)
* @where Query conditions
1 NULL NULL
2 There are query conditions do not take where
* @order ' 0 ' means desc ' 1 ' is ASC
* @pageCount Total Pages
*/
CREATE PROCEDURE Page
@currentpage int, @pagesize int,
@TableName varchar, @key varchar (30),
@where varchar, @order varchar (1),
@pageCount int, @str varchar (450) output
As
Begin
---------------The SQL statement executed------------
declare @sql nvarchar (@ordreby), nvarchar (200)
DECLARE @tempsql1 varchar, @tempsql2 varchar (200)
Total---------------Records-----------------
DECLARE @count int
---------------Temporary Variable------------------------
declare @temp1 int, @temp2 int
Set @TableName = ' + @TableName + '
Set @key = ' + @key + '
If @order = ' 0 '
Set @ordreby = ' ORDER BY ' + @key + ' desc '
Else
Set @ordreby = ' ORDER BY ' + @key
If @where = ' null '
Set @sql = ' Select @count = count (*) from ' + @TableName
Else
Set @sql = ' Select @count = count (*) from ' + @TableName + ' where ' + @where
------------@count Pay value (declaring variable @count in output type)---------------------------
EXEC sp_executesql @sql, N ' @count int out ', @count out
------------to find the total number of pages------------------------------
if (@count% @pagesize) =0
Set @pagecount = @count/@pagesize
Else
Set @pagecount = @count/@pagesize +1
-----------determine if the current page is displaying an exception------------------
If @currentpage > @pagecount
Set @currentpage = @pagecount
If @currentpage <1
Set @currentpage =1
The number of----------records is less than the number of pages displayed-----------------
if (@currentpage =1)
Begin
If @where = ' null '
Set @where = '
Else
Set @where = ' where ' + @where
Set @sql = ' SELECT top ' + str (@pagesize) + ' * from ' + @TableName + @where + @ordreby
End
Else
Begin
/**//*---------------Desc----------------------
* @temp1 represents the previous record
* @temp2 represents the following record
* Assuming a total of 77 records, take 10 at a time. Take 67~58 (page 2nd), remove the front 57 (1~57) and the back 10 (77~66)
*/
If @order =0
Begin
Set @temp1 = @count-@currentpage * @pagesize
If @temp1 <0
Set @temp1 =0
Set @temp2 = (@currentpage-1) * @pagesize
If @where = ' null '
Begin
Set @tempsql1 = ' SELECT top ' + str (@temp1) + "+ @key + ' from" + @TableName + ' ORDER BY ' + @key
Set @tempsql2 = ' SELECT top ' + str (@temp2) + "+ @key + ' from ' + @TableName + @ordreby
End
Else
Begin
Set @tempsql1 = ' SELECT top ' + str (@temp1) + "+ @key + ' from" + @TableName + ' where ' + @where + ' ORDER BY ' + @key
Set @tempsql2 = ' SELECT top ' + str (@temp2) + "+ @key + ' from ' + @TableName + ' where ' + @where + @ordreby
End
Set @sql = ' SELECT top ' + str (@pagesize) + ' ~ ' + @TableName + ' where ' + @key + ' not in '
Set @sql = @sql + ' (' + @tempsql1 + ') and '
Set @sql = @sql + @key + ' not in ' (' + @tempsql2 + ') '
If @where = ' null '
Set @sql = @sql + @ordreby
Else
Set @sql = @sql + ' and ' + @where + @ordreby
End
/**//*----------------ASC---------------------
* @temp represents the total number of records shown earlier
* Remove the @temp and remove the pagesize.
*/
Else
Begin
Set @temp1 = (@currentpage-1) * @pagesize
If @where = ' null '
Set @tempsql1 = ' SELECT top ' + str (@temp1) + "+ @key + ' from ' + @TableName + @ordreby
Else
Set @tempsql1 = ' SELECT top ' + str (@temp1) + ' + @key + ' from ' + @TableName + + ' where ' + @where + @ordreby
Set @sql = ' SELECT top ' + str (@pagesize) + ' ~ ' + @TableName + ' where ' + @key + ' not in '
Set @sql = @sql + ' (' + @tempsql1 + ') '
If @where = ' null '
Set @sql = @sql + @ordreby
Else
Set @sql = @sql + ' and ' + @where + @ordreby
End
/**//* -------------------------------------*/
End
Set @str = @sql
--exec sp_executesql @sql
End
Go
