I work on SQL server 2012 and web API entity framework .NET core 2.2 so I face issue I can't implement web API execute stored Procedure below
Create proc ItemCalculateStock @OptionId int=NULL, @ItemId int = NULL, @InventoryLocation int=NULL as beginif(@OptionId=1) beginSELECT i.itemName,l.InventoryName, SUM(case when QTY > 0 then QTY else 0 end) as PurchasedItem,SUM(case when QTY < 0 then -QTY else 0 end) as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining FROM [dbo].[Invenroty] n with(nolock) inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID inner join [dbo].[Items] i with(nolock) on n.itemid=i.id inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end and i.id=case when @ItemId is null then n.itemid else @ItemId end GROUP BY i.itemName,l.InventoryName end else beginSELECT i.itemName,l.InventoryName,PostingDate, case when QTY > 0 then QTY else 0 end as PurchasedItem,case when QTY < 0 then -QTY else 0 end as ConsumItems,case when QTY > 0 then QTY else 0 end - case when QTY < 0 then QTY else 0 end as remaining FROM [dbo].[Invenroty] n with(nolock) inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID inner join [dbo].[Items] i with(nolock) on n.itemid=i.id inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end and i.id=case when @ItemId is null then n.itemid else @ItemId end end end
so How to get result of stored procedure on web API using Entity Framework .NET core 2.2
[HttpGet("CalculateInventoryData")] public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation) { // here how to get stored procedure result here // so i ask question to know how to get result of stored procedure above }
to call API I use the link below :
https://localhost:44374/api/Inventory/getInventoryData?optionId=1&ItemId=2&InventoryLocation=1