Thursday, June 3, 2010

implement paging in SQL Server stored procedure

CREATE PROCEDURE [dbo].[GetMemberList]
@startIndex INT = 1,
@maxRecords INT = 10
AS
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @start_id INT, @total_rec INT;

SET ROWCOUNT @startIndex;

SELECT @start_id = Id, @total_rec = COUNT(Id) OVER() FROM Members
ORDER BY Id;

SET ROWCOUNT @maxRecords;

SELECT mem.*, @total_rec AS TotalRecords FROM Members mem
WHERE mem.Id <= @start_id
ORDER BY mem.Id;

SET ROWCOUNT 0;

END TRY
BEGIN CATCH
EXEC [IErrorLog];
END CATCH

No comments:

Post a Comment