Some storage processes can retrieve records of the specified page in the database, the page size can be specified by themselves, which can be suitable for applications that need paging display, more flexible. (From the book "Fast Track ASP.NET with C #")
Create Procedure SP_GETEMPLOYEESBYPAGE @ PAGENUMBER? INT, @ PAGESIZE? Intas
- create a temporary table with the columns we are interested inCREATE TABLE #TempEmployees (ID ?? int IDENTITY PRIMARY KEY, EmployeeID int, LastName nvarchar (20) ,? FirstName nvarchar (10) ,? Title??????? NVARCHAR (30) ,? TitleOfcourtesy? nvarchar (25), address ?? nvarchar (60) ,? city ?? nvarchar (15) ,? country nvarchar (15), ? Notes ?? ntext)
- fill the temp table with all the employeesINSERT INTO #TempEmployees SELECT EmployeeID, LastName (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, Address, City, Region, Country, Notes??????????)?? ,? First,? Titleofcourtesy,? Address,? City,? Region,? Country,? Notesfrom? Employees Order by EMPLOYEID ASC
- declare two variables to calculate the range of records to extract for the specified pageDECLARE @FromID intDECLARE @ToID int-- calculate the first and last ID of the range of records we needSET @FromID = ((@PageNumber - 1) * @ PageSize) 1Set @toid = @Pagenumber * @PageSize
- Select the page of recordsselect * from #tempemployees where id> = @Fromid and id <= @toidgo
