Okay, so I wanted to return a paged result set from a Stored Procedure in Microsoft SQL Server in T-SQL. The results in question can be easily fed via a web service endpoint to a Silverlight, DHTML or other dynamic grid. My own use is to populate a jqGrid.

The features I need are to be able to return only the relevant results for the current page, as well as a count of the total rows available. I need to be able to input the current page number, an arbitrary page size, as well as dynamically sort on a given column. My real world use is a fairly complex set of joined tables, with several input parameters, so I am going to limit me queries to one table with the following format:

CREATE TABLE MyList (
    [id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    [created] DATETIME NOT NULL DEFAULT GETDATE(),
    [name] NVARCHAR(100) UNIQUE NOT NULL DEFAULT '',
    [description] NVARCHAR(250) UNIQUE NOT NULL DEFAULT ''
);

More...