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