Paged SQL Queries

Monday, March 1, 2010 11:26:19 AM

This is mostly for my own memory, a procedure that returns paged results:

CREATE PROCEDURE [Messaging].[GetMessagesForUserPaged]
(
@MessageTo uniqueidentifier,
@StartRow int,
@Count int,
@SortExpression nvarchar(100)
)
AS

IF LEN(@sortExpression) = 0
    SET @sortExpression = 'DateSent DESC'

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT 
MessageID,
MessageFrom,
MessageTo,
DateSent,
Unread,
Subject,
Body,
MessageTypeID,
RowNumber
FROM (
SELECT 
MessageID,
MessageFrom,
MessageTo,
DateSent,
Unread,
Subject,
Body,
MessageTypeID,
ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ') AS RowNumber
FROM [Messaging].[Messages]
WHERE MessageTo = @MessageTo
) AS PagedRecords
WHERE RowNumber > ' + CONVERT(nvarchar(10), @StartRow) + ' AND RowNumber <= (' + CONVERT(nvarchar(10), @StartRow) + ' + ' + CONVERT(nvarchar(10), @Count) + ')'

-- Execute the SQL query
EXEC sp_executesql @sql

Comments


Leave Comment

  

  

  




Are you human? Prove it!