I really wanted to be able to send the page number i wanted and the number of records per page i wanted. so irregardless of what stage of things i was in i'd get the correct records. (and i added first name as searchable) 

ie: if i was looking at a data grid on page 5 and i had my records per page set to 50 and i changed to 30 and then to page 3, the sproc would get me page 3 of 30 records per page. Again i believe much of this functionality like ROW_COUNT and  Top (@variable) are mssql 2005 features.

USE [AdventureWorks]

GO

/****** Object:  StoredProcedure [dbo].[GetPersonContact]    Script Date: 05/10/2007 21:59:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter PROCEDURE [dbo].[GetPersonContactByName]

      (

      @length int,

      @page int,

    @firstname nvarchar(50),

    @lastname nvarchar(50)

      )

AS

declare @totalrows float

set @totalrows = ( SELECT  COUNT(*) from Person.Contact where LastName like (@lastname) and FirstName like (@firstname));

declare @lastrow int

set @lastrow = (@page - 1) * @length

declare @lastpage int

set @lastpage = CEILING(@totalrows / @length)

if (@page > @lastpage) set @page = @lastpage

if(@page < 1) set @page = 1;

      /* SET NOCOUNT ON */

WITH Ordered AS (SELECT     ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS rownum, ContactID, NameStyle, Title, FirstName, MiddleName,

LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate

FROM         Person.Contact where LastName like (@lastname) and FirstName like (@firstname))

    SELECT  TOP (@length) @page as Page, @lastpage as TotalPages,  ContactID, NameStyle, Title, FirstName, MiddleName,

LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate

     FROM         Ordered

     WHERE     rownum > @lastrow

return @totalrows