In this article, we will learn how to get the return value using the SQL stored procedure Output parameter. There are some scenarios where we need the return value using the SQL stored procedure such as whenever a new order is created into the database, then return an OrderId to the user for reference.

Let's consider the similar scenario and we have the following table OrderDetails which maintains the processed orders as shown in the following image.

I hope you have created the same table structure as shown above. Now create the stored procedures to get the return value as in the following code snippet.

Create PROCEDURE PlaceOrder  
(  
@Product varchar(50),  
@Amount decimal(18,2),  
@OrderId int out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
  
INSERT INTO [dbo].[OrderDetails]  
           (  
            Product,  
            Amount
           )  
     VALUES  
           (  
          @Product,  
          @Amount
           )  
select @OrderId=isnull(max(OrderId),0) from OrderDetails

END 

Summary

Related articles