MsSql Stored Procedure paging code [multiple articles collected]

Source: Internet
Author: User

Copy codeThe Code is as follows:
-- Usage instructions: This Code applies to MsSql2000 and is available for other databases, but not necessary.
-- Create a stored procedure
Create procedure pagination
@ TblName varchar (255), -- table name
@ StrGetFields varchar (1000) = '*', -- the column to be returned
@ FldName varchar (255) = '', -- Name of the sorted field (such as TABLE. FLDNAME format)
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ DoCount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (1500) = ''-- Query condition (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) -- Name of the sort field used for paging, excluding 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 indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ doCount
Else
Begin
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'ORDER BY' + @ fldName + 'desc'
-- If @ OrderType is not 0, execute the descending order. 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
-- Execute the above Code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strSQL the SQL code to be actually executed
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 + 'where' + @ strWhere +'' + @ strOrder + ') as tblTmp) and '+ @ strWhere + ''+ @ strOrder
End
End
Exec (@ strSQL)
Go
-- Test
Create table news -- create a table
(
N_id int iDENTITY (1, 1) primary key,
N_title char (200 ),
N_content text
)
-- Insert 1000000 data records in a write Loop
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', 0 ,''


Article 2

Copy codeThe Code is as follows:
A self-modified paging Stored Procedure
Create proc Paging
(
@ PageSize int,
@ PageIndex int,
@ PageField nvarchar (32 ),
@ CountTotal bit = 1,
@ FieldQuery nvarchar (512 ),
@ TableQuery nvarchar (512 ),
@ WhereQuery nvarchar (2048 ),
@ OrderQuery nvarchar (512)
)
AS
DECLARE @ bdate Datetime
SET @ bdate = getdate ()
DECLARE @ itemcount int
SET @ itemcount = @ pageIndex * @ pageSize
DECLARE @ itemlowwer int
SET @ itemlowwer = (@ pageIndex-1) * @ pageSize
DECLARE @ cmd nvarchar (3062)

IF @ pageIndex = 1
SET @ cmd = 'select TOP '+ CAST (@ pageSize as nvarchar) + ''+ @ fieldQuery + 'from' + @ tableQuery + 'where' + @ whereQuery + 'ORDER BY' + @ orderQuery
ELSE
SET @ cmd = 'select' + @ fieldQuery + 'from' + @ tableQuery + 'where' + @ pageField + 'in (select top '+ CAST (@ itemcount as nvarchar) + ''+ @ pageField + 'from' + @ tableQuery + 'where' + @ whereQuery + 'ORDER BY' + @ orderQuery + ')
AND '+ @ pageField +' not in (select top '+ CAST (@ itemlowwer as nvarchar) + ''+ @ pageField + 'from' + @ tableQuery + 'where' + @ whereQuery + 'ORDER BY' + @ orderQuery + ')'
-Print @ cmd
EXEC (@ cmd)

Select datediff (MS, @ bdate, getdate ())

IF @ countTotal = 1
BEGIN
SET @ cmd = 'select COUNT (0) FROM '+ @ tableQuery + 'where' + @ whereQuery
EXEC (@ cmd)
END
GO

Related Article

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.