Database paging using SQL Server 2005

This is a sample code that demonstrate database paging using SQL Server 2005. This code utilizes CTE and ROW_NUMBER() function. This code also allows passing the sort column as a parameter.

/*
Description: Sample script that shows paging using T-SQL Server 2005. It is utilizing CTE and the new function ROW_NUMBER()
Remarks:
*/

declare @page int;
set @page = 3;

declare @size int;
set @size = 4;

declare @sort_column varchar(50);
set @sort_column = 'last_name';

with result_set as (
    select
        row_number() over (
            order by
                case
                    when @sort_column = 'first_name' then first_name
                    when @sort_column = 'last_name' then last_name
                    when @sort_column = 'company' then company
                    when @sort_column = 'email' then email
                    else cast(no as varchar(50))
                end asc
        ) as row,
        first_name, last_name, company, email
    from dbo.contacts )

select *
from result_set
where row between (((@page * @size) - @size) + 1) and (@page * @size);

Happy coding!