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.
Post a Comment