USE [database]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ProcedureName]

(

        @I_IndexID    varchar(16),

        @I_PageIndex     INT,

        @I_PageSize        INT,

        @O_TotalCount    INT  output,

        @O_RTCD             int output,

        @O_APPMSG        varchar(512)  output,

        @O_SYSMSG         varchar(512)  output

)

AS

DECLARE    @ErrorMessage   nvarchar(4000);

DECLARE    @ErrorNumber     int;

DECLARE    @ErrorSeverity    int;

DECLARE    @ErrorState        int;

DECLARE    @ErrorLine          int;

DECLARE    @ErrorProcedure   int;

SET @O_RTCD=0;

SET @O_APPMSG='';

SET @O_SYSMSG='';

BEGIN TRY

     select @O_TotalCount = count(*)  from [datatable]

     select *,ROW_NUMBER() OVER (ORDER BY IndexID DESC) as RowID from

     (select * from  [datatable] where IndexID = @I_IndexID) as t1

     where RowID BETWEEN (@I_PageIndex - 1) * @I_PageSize + 1 AND @I_PageIndex * @I_PageSize;

END TRY

        BEGIN CATCH

        SELECT

          @ErrorNumber = ERROR_NUMBER(),

          @ErrorSeverity = ERROR_SEVERITY(),

          @ErrorState = ERROR_STATE(),

          @ErrorLine = ERROR_LINE();

        SELECT @ErrorMessage = N'Error %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();

        SELECT @O_RTCD= -1,

                     @O_APPMSG = 'Error '+CAST(@ErrorNumber as varchar) + ', Level '+ CAST(@ErrorSeverity as varchar) + ', State ' + CAST(@ErrorState as varchar) + ', Procedure'+ @ErrorProcedure + ', Line: ' +CAST(@ErrorLine as varchar),

                    @O_SYSMSG = ERROR_MESSAGE();

         PAISERROR(@ErrorMessage,@ErrorSeverty,1,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine);

END CATCH;