How To Get Return Value Using SQL Stored Procedure

In this article, we will learn how to get the return value using the SQL stored procedure Out 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 

Now execute the stored procedure using the SQL editor as follows.



In the preceding, The stored procedure inserts the new order into the SQL table and return the out value as OrderId using the stored procedure.

Summary

I hope from the preceding explanation, you have learned how to return the value using the SQL stored procedure. If you have any queries, then you can send using the following comment box.

Related articles


Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode