Return multiple record sets from stored procedure using Web API in AngularJS

Last Reply 3 months ago By skp

Posted 3 months ago

How to retrieve two output table data from a single stored procedure using web api and angularjs.

Sp :

USE [ERPT]
GO
/****** Object:  StoredProcedure [dbo].[GET_WH_RECEIPT_AUTO]    Script Date: 6/12/2019 10:13:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC GET_WH_RECEIPT_AUTO 
ALTER PROCEDURE [dbo].[GET_WH_RECEIPT_AUTO]
AS
BEGIN

declare @invoiceno varchar(50)

SELECT @INVOICENO = invoiceno FROM DISPATCH WHERE CUSTOMERID = 22 AND STATUS = 2 ORDER BY CREATEDDATE desc

DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME

SELECT @STARTDATE = DATEADD(MONTH,-7,STARTDATE),@ENDDATE = ENDDATE FROM FINANCIALYEAR where getdate() between startdate and enddate

--SELECT @INVOICENO 'InvNo'

SELECT
    DD.DISPATCHID, 
	DD.PARTID,
	DD.CUSTOMERID,
	--INVOICENO,
    PART.PARTNAME,
--	PART.CASTINGNUMBER,
	REPLACE(REPLACE(PART.PARTNO,'-',''),'.','') PARTNO,
	CONVERT(VARCHAR(10), DD.DISPATCHTIME, 103) 'InvoiceDate',
	DD.NUMBERS,
	DC.CURRENCYRATE,
	DC.CURRENCYAMOUNT,
	DC.INRRATE 'ExchangeRate',
	DC.INRAMOUNT,
	CAST((Dc.INRAMOUNT/DD.NUMBERS) AS DECIMAL(18,2)) 'UNITAMT',
	isnull(PALLETQTY, 0)'PalletCapacity',
	isnull(DD.NUMBERS/NULLIF(PALLETQTY,0),0) 'NoofPallets',
	DISPATCH.INVOICEID,
	WAREHOUSE.ISSUEDQTY,
	WAREHOUSE.STOCK,
	WAREHOUSE.STATUS
INTO #TEMPWH
 FROM DISPATCH_DAILY DD
 INNER JOIN DISPATCH_CURRENCY DC ON DC.DISPATCHID = DD.DISPATCHID
 INNER JOIN PART ON PART.PARTID = DD.PARTID 
 LEFT  JOIN part_pallet_master PPM ON PPM.PARTID = DD.PARTID AND PPM.CUSTOMERID = DD.CUSTOMERID
 LEFT  JOIN DISPATCH ON DISPATCH.INVOICEno = DD.INVOICEno AND DISPATCH.CREATEDDATE >= @STARTDATE
 LEFT JOIN WAREHOUSE ON WAREHOUSE.DISPATCHID = DD.DISPATCHID 
 WHERE DD.INVOICENO = @INVOICENO AND DISPATCHTIME >= @STARTDATE AND DISPATCHTIME <= @ENDDATE
 ORDER BY WAREHOUSE.STOCK DESC

 SELECT * FROM #TEMPWH

'Accepted',sum(ISSUEDQTY) 'Issued',sum(STOCK) 'Stock' FROM #TEMPWH

SELECT DISTINCT @INVOICENO invoiceno, invoiceid FROM #TEMPWH

drop table #TEMPWH
 
 end

 

You are viewing reply posted by: skp 3 months ago.
Posted 3 months ago

Finally, found the solution. Using dataset or datatable is always recommended, because it gives control to the code. Because, unspecified row data will not displayed. Dataset and datatable will eliminate those unspecified row data and display only the specified data.

Controller.cs (First ouput table data):

        [HttpGet]
        [ActionName("GetPendingDetails")]
        public string GetInvoiceDetails(DataSet ds)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GET_WH_RECEIPT_AUTO";
            sqlCmd.Connection = myConnection;
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            ds = new DataSet();
            string jsonString = string.Empty;
            myConnection.Open();
            sda.Fill(ds);
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
            return jsonString;           
        }

Controller.cs (Second ouput table data):

        [HttpGet]
        [ActionName("GetInvoiceProperties")]
        public string GetInvoiceProperties(DataSet ds)
        {           
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GET_WH_RECEIPT_AUTO";
            sqlCmd.Connection = myConnection;          
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            ds = new DataSet();
            string jsonString = string.Empty;           
            myConnection.Open();
            sda.Fill(ds);            
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[1]);
            return jsonString;           
        }