Posts

Showing posts from February, 2014

implement Pagination using store procedure in sql server

Implement pagination in store procedure in sql server.We need to pass Pagesize,PageIndex,sortValue,sortOrder as a input parameters the store procedure will give result based on input values

ALTER PROCEDURE [dbo].[proc_Name]
(
     @AdId            VARCHAR(1000)
    ,@PageIndex        INT    = 0
    ,@PageSize        INT    = 10  
    ,@SortByField    NVARCHAR(MAX) = 'Sorted Column Name'
    ,@SortOrder        NVARCHAR(5) = 'DESC'
)

AS
BEGIN  
    SET NOCOUNT ON;

    IF (ISNULL(@PageIndex, '') = '') SELECT @PageIndex = 0
    IF (ISNULL(@PageSize, '') = '') SELECT @PageSize = 10
    IF (ISNULL(@SortByField, '') = '') SELECT @SortByField = 'Sorted Column Name'
    IF (ISNULL(@SortOrder, '') = '') SELECT @SortOrder = 'DESC'

    DECLARE @OffsetCount INT

    SET @OffsetCount = @PageIndex * @PageSize


    IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL  DROP TABLE #tmpResult
    SELECT  …