To create a stored procedure returned to the specified strip record, you must first specify the number of records that return the result set, you can use the temporary table, you can also use the Table variable (SQL Server 2000), two in performance, there is not much difference, However, the Table variable is stored in memory. If your server is not included, you can consider using a temporary table, a temporary table using a hard disk storage result, and the temporary table needs to manually release the object, and the Table variable is automatically released after the stored procedure. . Below is the stored procedure we have to create:
Create Proc getAuthors @ Author_last_name as varchar (100) = null, @ Startrow as int = null, @ stoprow as int = NULLAS
---- Building a Table Variable Declare @t_Table Table ([Rownum] [INT] Identity (1, 1) PRIMARY NOTNTINTITY (1, 1), [VARCHAR], [VARCHAR], [VARCHAR] (20), [Phone] [25), [Address] [varchar] (40), [City] [varcha] (20), [State] [char] (2), [ZIP] [char] (5))
---- Stop processing query set rowcount @stoprow after returning the specified @stoprow row
---- Insert into the Table variable insert @T_TABLE ([Author_First_name], [Author_First_name], [Phone], [Address], "], [State], [ZIP]) SELECT [Author_Last_name], [Author_First_name], [Phone], [Address], [City], [State], [ZIP]
From authorswhere author_last_name like '%' @Author_last_name '%' Order by Author_Last_Name
---- Return to the correct result Select * from @T_Table where rownum> = @startroworder by rownum
Go
Parameters @startrow and @stoprow Receive integer values, representing the start recording and end record to return, if you want to return to page 4 in a 25 recorded page, we can set @startrow to 76, @ stoprow is 100. We define a column called ROWNUM in Table variable @t_table, and specify as the identifier column, which is important in the paging technology we introduced here. When we insert data, this column is automatically increased. It will be sorted when the data is inserted. Set rowcount statement is critical to optimization performance, telling SQL Server to limit data to be inserted, if we want 76-100 records between records, then you may not need to insert larger than 100 records. The last SQL statement selects ROWNUM greater than or equal to @startrow from @T_table, then returns them to the web server, binds them to the DataGrid object by the web server. It is worth noting that if you want to get 76 to 100 recorded data, we must insert 100 recorded data into the Table variable, which means: If the number of pages requested by the viewer is getting bigger and bigger, the page performance will also have Dropped. For example: To display data on page 100 (recorded from paragraph 2451 to section 2500), we must first populate 2500 records to Table variables or temporary tables, so performance relies on your computer's hardware and you want to return. The number of records, the test indicates that the use of such a stored procedure in SQL Server 2000 returns to page 100 within 200-250 milliseconds, while returning to the first page only needs to be 4 milliseconds. Even if it returns data on page 56 (recorded from 12451 to 12500), it can be completed within 650 to 750 milliseconds. It should be said that this situation is very few. However, in order to reduce the pressure of the database and network transmission, the number of designed query results pages is very effective. Excerpt from http://dotnet.aspx.cc/showdetail.aspx?id=108b1516-53ce-4357-b061-17295af9689f
