Pages

Showing posts with label implement paging in SQL Server stored procedure. Show all posts
Showing posts with label implement paging in SQL Server stored procedure. Show all posts

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