ahmed elbarbary

ahmed elbarbary

  • 952
  • 1.5k
  • 109k

How to write web Api execute stored procedure ?

Jul 6 2021 11:10 AM

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


Answers (7)