CREATE PROCEDURE DataWithPagingAndSorting
--Add the parameters for the stored procedure here
@insPageNo SMALLINT = 1,
@insPageSize SMALLINT = 10,
@SortType varchar(128) = 'DT',
AS
BEGIN
DECLARE @intTotalCount INT,
@intPageCount INT
SET NOCOUNT ON
SELECT @intTotalCount = COUNT(Id) from TableName
SET @intPageCount = @intTotalCount/@insPageSize;
IF (@intTotalCount%@insPageSize<>0)
SET @intPageCount= @intPageCount+1
IF (@insPageNo>@intPageCount)
SET @insPageNo=@intPageCount
SELECT TOP (@insPageSize) *
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY
CASE WHEN @SortType = 'NM' THEN Name END
,CASE WHEN @SortType = 'EM' THEN [Email] END
,CASE WHEN @SortType = 'MN' THEN ContactNo END
,CASE WHEN @SortType = 'CC' THEN Country END
,CASE WHEN @SortType = 'DT' THEN CreDate END DESC
) AS RowNum,
M.Id [ID], [Name],M.Add1, M.Add2,
M.Town, M.PostCode, M.ContactNo, M.CreDate, M.ActDate , Country, M.Email
from TableName M
) A
WHERE A.RowNum > (@insPageSize * (@insPageNo - 1))
SELECT @intTotalCount as TotalCount;
END
