Also talk about the storage process paging

zhaozj2021-02-16  52

Recently tested

Xiao Spring

written

Memory paging

I found some questions: I have an Test (ID, Name, FID) to add dozens of data to Test, make id = 1, 2, 3, 4 ......... (ie incremented Integer) Others calls the following parameters for the change in T-SQL DEBUGGER, respectively: @ querystr = * from test @ Keyfield = [id] @ Pagesize = 3 @ Pagenumber = 1 problem out, see output results (Note ID): ID Name FID 4 KWKLOVER 2 5 KWKLOVER 2 6 KWKLOVER 2 According to the incoming parameters, our expectations should be: ID Name FID 1 KWKLOVER 2 2 KWKLOVER 2 3 KWKLOVER 2

Below is the paging stored procedure for the memory points I refer to Xiaochun, which can solve the above problem: Create Procedure PrgetRecordbyPage (@PageSize Int, - Number of records @PAGENUMBER INT, - Current page @Querysql varchar (1000) Some query strings, such as * from test order by id desc @keyfield varchar (500)) asbegin

Declare @sqltable as varchar (1000) Declare @sqltext as varchar (1000)

Set @ SqlTable = 'SELECT TOP' CAST (@ Pagenumber * @ PageSize As Varchar (30)) '' @ querysql set @ Sqltext = 'SELECT TOP' CAST (@PageSize As Varchar (30)) '* From ' ' (' @ Sqltable ') as tembtba ' ' Where ' @ Keyfield ' Not in (SELECT TOP ' CAST ((@ Pagenumber-1) * @ PageSize As Varchar (30)) ' @ Keyfield 'from' '(' @ SqlTable ') as temptbb)' EXEC (@sqltext)

Endgo

As for the efficiency, huh, I don't know, because I still don't know how to test the operational efficiency of the stored procedure, I hope that all friends can enlighten me! Thank you first.

转载请注明原文地址:https://www.9cbs.com/read-15640.html

New Post(0)