Custom Paging using Stored procedure with multiple tables in SQL Server

lingers
 
on Jun 06, 2021 11:58 PM
548 Views

How do i use Paging in ASP.Net GridView using jQuery AJAX for joining two tables.

Please find below the code for Paging in ASP.Net GridView using jQuery AJAX for customers table which is working, I want to add 3 more columns from orders table  in the gridview.

how do i go about it column 1 = orderid column 2 = ordertype column 3 = orderstatus

CREATE PROCEDURE [dbo].[GetCustomers_Pager]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[City]
      INTO #Results
      FROM [Customers]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END

please help

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 07, 2021 12:09 AM

Hi lingers,

Refer below query.

SQL

ALTER PROCEDURE [dbo].[GetCustomers_Pager]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY c.[CustomerID] ASC
      )AS RowNumber
      ,c.[CustomerID]
      ,c.[CompanyName]
      ,c.[ContactName]
      ,c.[City]
	  ,o.[OrderID]
	  ,o.[OrderType]
	  ,0.[OrderStatus]
      INTO #Results
      FROM [Customers] c
	  INNER JOIN Orders o ON c.CustomerID = o.CustomerID
      
      SELECT @RecordCount = COUNT(*)
      FROM #Results
            
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
      
      DROP TABLE #Results
END