Share a common paging SQL

A long time no blog, today records a SQL Server universal paging stored procedure (for SqlServer2000 and above)

1. Support for connecting tables

2. Support Conditional Query

Use [MYDB] GO

/****** object:storedprocedure [dbo]. [Sp_commonpage]

SET QUOTED_IDENTIFIER on GO

--------------------------------------Purpose: Paging stored procedures (extremely efficient for tables with primary keys)--Description:------------------------------------

CREATE PROCEDURE [dbo]. [Sp_commonpage]

@tblName varchar (255),--table name

@fldName varchar (255),--primary key field name

@PageSize int = 10,--page size

@PageIndex int = 1,--page

@IsReCount bit = 0,--Returns the total number of records, and a value other than 0 returns

@OrderType bit = 0,--set sort type, not 0 value descending

@strWhere varchar (1000) = "--Query criteria (note: Do not add where)

As

DECLARE @strSQL varchar (6000)--the subject sentence

DECLARE @strTmp varchar (100)--Temporary variable (may error when query condition is too long, can be modified by 100 to 1000)

DECLARE @strOrder varchar (400)--Sort type

If @OrderType! = 0

Begin

Set @strTmp = ' < (select Min ')

Set @strOrder = ' ORDER by [' + @fldName + '] desc '

End

Else

Begin

Set @strTmp = ' > (select Max ')

Set @strOrder = ' ORDER by [' + @fldName + '] ASC '

End

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + '] ' + @strTmp + ' ([' + @fldName + ']) from (select top ' + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as tbltmp) ' + @strOrder

If @strWhere! = "

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + '] ' + @strTmp + ' ([' + @fldName + ']) from (select top ' + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @ Tblname + '] where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder

If @PageIndex = 1

Begin

Set @strTmp = ' '

If @strWhere! = "

Set @strTmp = ' where ' + @strWhere

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * from [' + @tblName + '] ' + @strTmp + ' + @strOrder

End

If @IsReCount! = 0

Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' + ' where ' + @strWhere

EXEC (@strSQL)

GO