Application of two MSSQL paging stored procedure instances
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>
<title> two kinds of MSSQL paging stored procedure instance application </title>
<body>
MSSQL paging in small data Web site applications, do not need to save stored procedures, only tens of thousands, tens database tutorial will use the stored procedures,
Creating a Stored Procedure
CREATE PROCEDURE Pagination
@tblName varchar (255),--table name
@strGetFields varchar (1000) = ' * ',--columns to be returned
@fldName varchar (255) = ',--sorted field name (may include Table.fldname form)
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount bit = 0--Returns the total number of records, not 0 values.
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1500) = '--Query criteria (note: Do not add where)
As
DECLARE @strSQL varchar (5000)--subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type
DECLARE @fldName_t varchar (255)--The name of the sort field used when paging, without the table name when multiple tables are tied
Set @fldName_t = Right (@fldName, Len (@fldName)-charindex ('. ', @fldName))
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 over 0, the total count is executed. All of the following code is @docount 0
Else
Begin
If @OrderType!= 0
Begin
Set @strTmp = ' < (select Min '
Set @strOrder = ' ORDER BY ' + @fldName + ' desc '
If @ordertype is not 0, it is important to perform descending order.
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 + ' + ' + @strO Rder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + "+ @strGetFields + ' from ' + @tblName + ' +" + @strOrder
--If the first page executes the above code, this will speed up execution
End
Else
Begin
--The following code gives @strsql the SQL code to actually execute
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ' + @tblName + ' where ' + @fldName + ' + ' + @strTmp + ' (' + @fldName_t + ') 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_t + ') from (select Top + str (@PageIndex-1) * @PageSize) + "+ @fldName + ' from ' + @tblName + ' wher E ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End
EXEC (@strSQL)
Go
--Test
CREATE TABLE News--Building tables
(
n_id int IDENTITY (1,1) primary key,
N_title char (200),
N_content text
)
--Write loop inserts data from article 1 million
CREATE proc TT
As
DECLARE @i int
Set @i=0
while (@i<1000000)
Begin
INSERT INTO News (n_title,n_content) VALUES (' SB ', ' DSFSDFSD ')
Set @i=@i+1
End
EXEC TT
exec pagination ' news ', ' * ', ' n_id ', 1000,2,0,0, '
MSSQL paging stored procedures, supporting complex queries such as connection queries
Call method
exec pagination ' SELECT * from [order] ', 0, ' Createtime ASC '
exec pagination ' SELECT * from [order] ', 0, ' Createtime ASC, Money DESC '
Instance procedure
CREATE PROCEDURE Pagination
(
@SQL nvarchar (1024),--query statement
@PageSize int = 20, paging size
@PageIndex int = 0,--Paging index
@Sort nvarchar (100) = ',--sort field
@TotalCount int = 0 OUTPUT-Total
)
As
SET NOCOUNT ON
/* DECLARE query String * *
declare @strSQL nvarchar (4000)
Set @strSQL = ' Select @TotalCount =count (*) from (' + @SQL + ') as T '
/* Get the total number of query results * *
EXEC sp_executesql
@strSQL,
N ' @TotalCount int=0 OUTPUT ',
@TotalCount = @TotalCount OUTPUT
DECLARE @ItemCount int
DECLARE @_pageindex int
Set @_pageindex = @PageIndex + 1;
/* Determine the search boundary * *
Set @ItemCount = @TotalCount-@PageSize * @_pageindex
if (@ItemCount < 0)
Set @ItemCount = @ItemCount + @PageSize
Else
Set @ItemCount = @PageSize
if (@ItemCount < 0) return 1
if (@Sort!= ')
Begin
/* Declare the sort variable * *
declare @IndexSort1 nvarchar (nvarchar), @IndexSort2 (50), @Sort1 nvarchar
SET @Sort1 = @Sort
SET @Sort2 = replace (replace (@Sort, ' DESC ', ' @SORT '), ' ASC ', ' DESC '), ' @SORT ', ' ASC ')
Set @strSQL = ' SELECT * FROM
(SELECT top ' + STR (@ItemCount) + ' * FROM
(SELECT top ' + STR (@PageSize * @_pageindex) + ' * "from
(' + @SQL + ') As T0
ORDER BY ' + @Sort1 + ') as T1
ORDER BY ' + @Sort2 + ') as T2
ORDER BY ' + @Sort
End
Else
Begin
Set @strSQL = ' SELECT * FROM
(SELECT top ' + STR (@ItemCount) + ' * FROM
(SELECT top ' + STR (@PageSize * @_pageindex) + ' * "from
(' + @SQL + ') As t0)
as T1)
As T2 '
End
EXEC sp_executesql
@strSQL
Go
?
/*
The benefit of the stored procedure is that the function is the MSSQL internal function that runs quickly.
*/?>
</body>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
