Developing T-SQL Stored Procedures to Deal with Lookup Tables - How to retrieve a set of rows page by page using a T-SQL stored procedure
(Page 5 of 5 )
If you need to retrieve a set of rows on a page-by-page basis, you may need to consider the following stored procedure:
CREATEPROCEDURE dbo.db_emp_ListByPage
@CurrentPage As tinyint,
@PageSize As tinyint
AS
Declare@FirstRec int
Declare@LastRec int
Set@FirstRec = (@CurrentPage - 1) * @PageSize
Set@LastRec = (@CurrentPage * @PageSize + 1)
CREATETABLE #empTemp
(
AutoId int IDENTITY PRIMARY KEY,
empno int,
ename nvarchar(50),
sal float,
deptno int
)
INSERTINTO #empTemp
(
empno,ename,sal,deptno
)
SELECT
empno,ename,sal,deptno
FROM emp
SELECT
empno, ename, sal, deptno
FROM #empTemp
WHERE
AutoID > @FirstRec
AndAutoID < @LastRec
The above code uses a temporary table in the database to page the rows. For huge tables, the above method may not work well. And there exists several efficient ways to deal with the paging of rows using stored procedures. I shall dedicate one article exclusively to the paging of rows using T-SQL stored procedures.
Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
