How to get four output table data in web api using c# with the help of stored procedure.
This return four output tables.
StoredProcedure :
USE [ERPT]
GO
/****** Object: StoredProcedure [dbo].[GET_WH_RECEIPT] Script Date: 6/4/2019 2:13:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC GET_WH_RECEIPT @INVOICENO = '3377'
ALTER PROCEDURE [dbo].[GET_WH_RECEIPT]
@INVOICENO VARCHAR(50)=NULL
AS
BEGIN
DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME
SELECT @STARTDATE = DATEADD(MONTH,-7,STARTDATE),@ENDDATE = ENDDATE FROM FINANCIALYEAR where getdate() between startdate and enddate
SELECT
DD.DISPATCHID,
DD.PARTID,
DD.CUSTOMERID,
--INVOICENO,
PART.PARTNAME,
-- PART.CASTINGNUMBER,
PART.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',
PALLETQTY 'PalletCapacity',
DD.NUMBERS/NULLIF(PALLETQTY,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
SELECT SUM(NUMBERS) 'TotalQty' FROM #TEMPWH
SELECT SUM(NUMBERS) 'Accepted',sum(ISSUEDQTY) 'Issued',sum(STOCK) 'Stock' FROM #TEMPWH
SELECT DISTINCT invoiceid FROM #TEMPWH
drop table #TEMPWH
end