Paging - Returning a Subset of a Recordset

CREATE PROCEDURE prc_lov_suppliers
@Suppliers                nvarchar(30) ,
@OrderBychar(20)  = "Suppliers",
@Directionbit = 0,
@Pageint = 1,
@RecsPerPageint = 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
@Pageint,
@RecsPerPageint,
@Recordsint
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