Hi Friends, In this tutorial, I am going to show you how to use the Output parameter in SQL Server. Output parameter in SQL Server is used to return single or multiple values from the Stored Procedure.

Output Parameter in SQL Server:

Output parameter in SQL Server is used to return single or multiple values from the Stored Procedure. Defining a Stored Procedure with output parameter is very similar to defining a stored procedure with the input parameter, the only difference is that you use the OUTPUT or OUT keyword to define an output parameter.

Syntax for defining Output Parameter:

Explanation:

if you see the above syntax then you can understand defining a stored procedure with output parameter is very similar to defining a stored procedure with the inputparameter.  Where @param1 is an input parameter which is not defined as an output parameter and @param2, @param3 are output parameters

Let’s see a couple of examples using the Output parameter in SQL Server. Before starting let’s create an Example table and insert some dummy data.

Create Table:

Insert Records:

 

Example 1:

Explanation:

The stored procedure I have created will accept EmployeeID as input parameter and return EmployeeName, Salary, and EmployeeAddress as an output parameter. I have marked all these three parameters as Output.

Executing the Stored Procedure in SQL Server:

Output::

 

Output Parameter

 

Example 2:

Output:

 

Output

Executing this Stored Procedure from C# code:

Create a C# Console Application called SQLServerOutputParameter. Write the following code in Program,cs file.

 

Please replace your data source name in the above example:

 

Output:

Output Parameter

Code Explanation:

From the above C# code you can see that to use an output parameter in C# we need to set that parameter Direction to Output as shown below.

cmdGetEmployeeDetails.Parameters[“@Salary”].Direction = ParameterDirection.Output;

 

View More:

Conclusion:

I hope you would love this post. Please don’t hesitate to comment for any technical help. Your feedback and suggestions are important to me.

Thank You.

Leave a Comment