CREATE Procedure Page
@tblname varchar (255), - Table name
@StrGetfields varchar (1000) = '*', - the column that needs to be returned
@fldname varchar (255) = '', - Sort field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Page
@docount bit = 0, - Return to the total number of records, non-0 values return
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = "SELECT Count (*) As Total from [" @tblname "] where"
@ strwhere
Else
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
end
- The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0
Begin
Set @strtmp = "<(SELECT MIN"
Set @strorder = "Order by [" @fldname "] desc"
- If @ORDERTYPE is not 0, it will be designed, this sentence is very important!
end
Else
Begin
Set @strtmp = "> (SELECT MAX"
Set @strorder = "Order by [" @fldname "] ASC"
end
IF @PageIndex = 1
Begin
IF @strwhere! = ''
Set @strsql = "SELECT TOP" STR (@PageSize) ""
@ strGetfields "from [" @tblname "] where" @strwhere "" @strorder
Else
Set @STRSQL = "SELECT TOP" STR (@Pagesize) " @ strGetfields " from [" @tblname "] " @strorder
- If you are the first page, you will implement the above code, which will speed up the execution speed.
end
Else
Begin
- The following code gives @strsql to real execute SQL code
Set @strsql = "SELECT TOP" STR (@PageSize) ""
@ strGetfields "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) ""
@ strGetfields "from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "["
@fldname "] from [" @tblname "] where" @strwhere ""
@strorder ") as tbltmp) and" @strwhere "" @strorder
end
end
EXEC (@strsql)
Go
