Stored procedure paging Another method (using the table variable) (pick)

variables | stored Procedures | Pagination to create a stored procedure that returns the results of a specified record, you must first specify the number of records to return the result set, either with a temporary table or with a table variable (SQL Server 2000), and the two are not significantly different in performance, but Table variables are stored in memory, if your server memory is not much, you can consider using temporary tables, temporary tables using hard disk storage results, temporary tables need to manually release objects, and table variables are automatically released after the end of the stored procedure.
Here is the stored procedure we want to create:

Create proc GetAuthors
@Author_Last_Name as varchar = NULL,
@StartRow as int = null,
@StopRow as int = null
As

----to establish a table variable with an identifier column
DECLARE @t_table table
(
[RowNum] [INT] IDENTITY (1, 1) Primary key not NULL,
[Author_last_name] [varchar] (40),
[Author_first_name] [varchar] (20),
[Phone] [Char] (12),
[Address] [varchar] (40),
[City] [varchar] (20),
[State] [Char] (2),
[Zip] [Char] (5)
)

----Stop processing the query after returning the specified number of @stoprow rows
Set RowCount @StopRow

----inserted into the table variable
Insert @t_table
(
[Author_last_name],[author_first_name],[phone],[address],[city],[state],[zip]
)
SELECT [Author_last_name],[author_first_name],[phone],[address],[city],[state],[zip]

From authors
WHERE author_last_name like '% ' + @Author_Last_Name + '% '
ORDER BY Author_last_name

----Return to the correct result
SELECT * from @t_table WHERE rownum >= @StartRow
ORDER BY RowNum

Go

Parameters @startrow and @stoprow receive an integer value, representing the start and end records to return, and if you want to return 4th page in a 25-record page, we can set @startrow to be the same, @StopRow to 100. We defined a column called RowNum integer type in table variable @t_table and specified as an identifier column, which is important in the paging technology we're introducing here, which automatically increases when we insert data, and it will sort when inserting data. The SET ROWCOUNT statement is critical to optimizing performance, telling SQL Server to limit the data to be inserted, and if we want data between 76-100 records, you don't have to insert data larger than 100 records. The final SQL statement selects rownum greater than or equal to @startrow from the @t_table table variable, and then returns them to the Web server, which is bound to the DataGrid object by the Web server. It is worth noting that if you want to get data from 76 to 100 records, we have to insert 100 records into the table variable, which means that the page performance will be reduced if the number of pages requested by the viewer becomes larger. For example, to display the data on page 100th (from No. 2451 to No. 2500), we have to populate the table variable or temporary table with 2,500 records, so performance depends on your computer's hardware and the number of records you want to return, and tests show that in SQL Server Using such a stored procedure in 2000 returns the 100th page on average within 200-250 milliseconds, and it takes only 4 milliseconds to return to the first page. Even the data returned on page No. 500 (from the 12,451th to 12,500th record) can be completed in 650 to 750 milliseconds. It should be said that this situation is rarely seen. However, in order to reduce the pressure of database and network transmission, it is very effective to design reasonable query result pages.

Excerpt from Http://dotnet.aspx.cc/ShowDetail.aspx?id=108B1516-53CE-4357-B061-17295AF9689F