Application of two MSSQL paging stored procedure instances

Source: Internet
Author: User
Tags mssql

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>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.