ALTER PROCEDURE SP_Files_Select 
( 
@FileID INT = NULL, 
@StatusID INT = NULL, 
@StartIndex INT, 
@PageSize INT, 
@TotalCount INT = NULL OUTPUT 
) 
AS 
BEGIN 
SET NOCOUNT ON; 

DECLARE @EndIndex INT 

IF @StartIndex <= 1 
BEGIN 
SET @StartIndex = 0 
END 
ELSE 
BEGIN 
SET @StartIndex = (@StartIndex - 1) * @PageSize 
END 

SET @EndIndex = @StartIndex + @PageSize 

SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS SNO 
,[FileID] 
,[FileLocation] 

FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS Row, 
,[FileID] 
,[FileLocation] 
FROM [Input_Files]
WHERE (@FileID IS NULL OR FileID = @FileID) AND 
(@StatusID IS NULL OR StatusID = @StatusID)) AS RecordRows 
WHERE Row > @StartIndex AND 
Row <= @EndIndex 
 

SELECT @TotalCount = COUNT (1) 
FROM [Input_Files] 
WHERE (@FileID IS NULL OR FileID = @FileID) AND 
(@StatusID IS NULL OR StatusID = @StatusID) 

END