Mssql stored procedure page (total number of pages returned at the same time)

Mssql stored procedure page (total number of pages returned at the same time)

There is no convenient limit paging like MySql. If you need to query data by page in MSSQL, you have to write the stored procedure...

The not in method has poor paging performance. It is better to create a variable table. (MSSQL2000)

Previously, we used to retrieve paging data and separate the total number of retrieved data, that is, to perform two queries.

One day, I suddenly thought about it. I simply wrote the two operations into the paging query. When the paging data is returned, the total number of data is also returned.

What are the benefits of doing so?

1. Reduce the number of database tutorial requests.

2. When there are many query conditions, you only need to spell the SQL statement once.

3. Easy maintenance

4 ,......

 

These reasons are enough, so let's do it.

 

-- ===================================================== ======
-- Author: Henson
-- Create date: 2011-06-01
-- Description: returns the total number of data that meet the query conditions and the number of data records on the current page.
-- ===================================================== ======
Create procedure [dbo]. [ST_GetPagedData]
@ MbCd varchar (12), -- Member ID
@ MbName varchar (20), -- member name
@ PageSize int, -- number of records displayed on each page
@ CurrPage int, -- Index of the current page
@ RecordCount int output -- Total number of records (output parameter)
AS
BEGIN
Declare @ strSql varchar (4000)
Declare @ strWhere varchar (1000)
-- Generate the Where condition based on the parameter
Set @ strWhere =''
If (isnull (@ MbCd ,'')! = '')
Begin
Set @ strWhere = @ strWhere + 'And MB_CD = ''' + @ MbCd + ''''
End
If (isnull (@ MbName ,'')! = '')
Begin
Set @ strWhere = @ strWhere + 'And Name like ''%' + @ MbName + '% '''
End
   
   
-- Retrieve the total number of records
Declare @ strCountSql nvarchar (1000)
Set @ strCountSql = 'SELECT @ RecordCount = count (*) From M_Account a Where 1 = 1' + @ strWhere
Exec sp_executesql @ strCountSql, n' @ RecordCount int output', @ RecordCount output
-- Print (@ strCountSql)
   
-- Get data
   
Declare @ topIdNum int, @ delNum int
Set @ topIdNum = @ PageSize * @ CurrPage
Set @ delNum = (@ CurrPage-1) * @ PageSize
   
Set @ strSql ='
DECLARE @ temptb table
        (
Uid int identity (1, 1 ),
Mb_Cd varchar (12)
        )
       
Insert Into @ temptb (Mb_Cd)
Select Top '+ cast (@ topIdNum as varchar (10) + 'A. Mb_Cd,
From M_Account
Where 1 = 1' + @ strWhere + N'

Delete from @ temptb where uid <= '+ cast (@ delNum as varchar (10) + N'

Select *
From M_Account
Inner Join @ temptb B On a. Mb_Cd = B. Mb_Cd
        '
       
Exec (@ strSql)
   
END

 

Well, that's it.

Then, when you call the API, you can obtain the value of the output parameter.