Skip to main content

Stored procedure for paging with sorting


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

Comments

Popular posts from this blog

IIS, There was an error when trying to connect. Do you want to retype your credentials and try again?

IIS 8 error on windows server 2012 insufficient permission There was an error when trying to connect. Do you want to retype your credentials and try again? Details: Filename: \?\C:Windows\system32\inetsrv\config\redirection.config Error: Cannot read configuration file due to insufficient permissions screenshot: Solution: Steps to short out this issue: go to C:\Windows\Microsoft.Net\Framework64\v2.0.50727\CONFIG\machine.config here you got the redirection tag like the below <configurationredirection enabled="true" password="[enc:IISCngProvider:X0ObCWwZ4+PrTHiFVPtzFeCcL8u5P6KUOYfo1/0QrgZWATA5pKWqHvD8nL2crNJKyyqr4z/rBdLPjdRcaLxAMMj4l+lvp5EXXKSXueolvyGa34F4QZfbBVCM6oVNcq3M368TOTVjJv4POVFQWvu0MDVlGgReglXB+Lw5BRI4Htw=:enc]" path="C:\Windows\System32\inetsrv\config\import\" username="Administrator"> </configurationredirection"></li"> you need to change this to <configurationRedirection /> Th...

Plesk - Upgrade the .net Framework to 4.7.2 OR 4.8

 Steps to Upgrade the .net Framework to 4.7.2 OR 4.8 1. First Download the .NET Framework from https://dotnet.microsoft.com/download/dotnet-framework   2. Install on the Server. 3. Login to the Plesk and go to "Tools & Settings" > "Server Components" and refresh the components using the refresh button 4. After update, it will reflect in "Web Script" section at the server component page. All done!!!