Recently, I found that many of my friends have read the code of the mssql Stored Procedure paging, And I have sorted it out for you. I hope this will help you.
Recently, I found that many of my friends have read the code of the mssql Stored Procedure paging, And I have sorted it out for you. I hope this will help you.
The 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
The 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
