Paging - Returning a Subset of a Recordset

CREATE PROCEDURE prc_lov_suppliers
	@Suppliers                nvarchar(30) ,
	@OrderBy		char(20)  = "Suppliers",
	@Direction		bit = 0,
	@Page			int = 1,
	@RecsPerPage		int = 10
AS 

SET NOCOUNT ON 

/* Add the % to the @Suppliers parameter because I use this to filter my selection*/
DECLARE @Search nvarchar(30)
SELECT @Search =  @Suppliers+"%"		

/* Create your temp table without any identity columns and only include the columns required*/
CREATE TABLE #TempItems			
(	SupplierID int,
	CompanyName varchar(50) )


/* insert selection into temp table with specified order and remove any identity property*/
if @Direction = 0
	INSERT INTO #TempItems
	SELECT CAST(SupplierID AS int) as SupplierID,  /*removing of identity property from column*/
		CompanyName
	from Suppliers
	WHERE CompanyName LIKE @Search
	order by CompanyName asc
else
	INSERT INTO #TempItems
	SELECT CAST(SupplierID AS int) as SupplierID,  /*removing of identity property from column*/
		CompanyName
	from Suppliers
	WHERE CompanyName LIKE @Search
	order by CompanyName desc

/* Now we alter the table and add an identity column for use in our paging
The execute command must follow the alter table command so that we can use the @@Rowcount variable which
will reflect the number of records in the temp table*/

alter table  #TempItems add  [id]  int identity
execute prc_lov_Suppliers_R @Page,  @RecsPerPage, @@Rowcount

SET NOCOUNT OFF
RETURN @@ERROR


CREATE PROCEDURE prc_lov_Suppliers_R
	@Page				int,
	@RecsPerPage			int,
	@Records			int
AS 

SET NOCOUNT ON 

/* returns a specific page of data and the record count */
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

select @Records as Records,
	SupplierID,
	CompanyName
From #TempItems
WHERE id > @FirstRec AND id < @LastRec

SET NOCOUNT OFF

RETURN @@ERROR