GridView + paging Stored Procedure

There can be only two parameters in the stored procedure: the page number and the number of lines per page
A simple select count (1) FROM TableName can be used for the total number of rows. The total number of rows is the total number of rows per page.
Fill the calculated numbers in the corresponding properties of the GridView. When you click the page number, you can call the paging stored procedure to refresh the data.
Group owner (87273411) 09:34:52
Create PROCEDURE Sp_Conn_Sort
(
@ TblName varchar (255), -- table name

@ StrGetFields varchar (1000) = '*', -- the column to be returned

@ FldName varchar (255) = '', -- Name of the sorted Field

@ PageSize int = 40, -- 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

 

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 + ') 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)
Group owner (87273411) 09:35:07