How to use out parameter in SQL server stored procedure

As I can see your question is single but it is containing three questions in the part as bifurcated below:

1. How to use the output parameter in SQL Server.

2. How to use the try-catch in SQL Server and

3. How to call the procedure containing an output parameter.

Below are the solutions for that: Using an output parameter in SQL Server stored procedure and also using the try-catch in the stored procedure.

CREATE PROC usp_divide(
@a decimal,
@b decimal,
@c decimal output
) AS
BEGIN
BEGIN TRY
SET @c = @a / @b;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;

To call a SQL Server procedure which contains an ouptput parameter you need to create a variable of the desired data type as defined in the procedure and pass same as shown below.

declare @r decimal(18, 2)
exec usp_divide 50, 10, @r output
exec usp_divide 5, 0, @r output

Here the output of the first query will be 5 while the output of second query is as shown below.

ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 usp_divide 8 Divide by zero error encountered.

Here there are already some of the functions available in SQL Server such as Error_number(), Error_Line(), etc.

If your output parameter is of the varchar i.e., string type then you can set it in cath block with concatenating above message such as

SET @c = 'The error is Error Number: ' + ERROR_NUMBER() + ', '+ 'Error Line: ' + ERROR_LINE() + 'Error Message' + Error_Message()

Now, to call a procedure that contains an output parameter you need to pass the values of the parameter in the below way.

In C#

objdbCmd = ObjDB.GetStoredProcCommand("YourProcedureName");
ObjDB.AddInParameter(objdbCmd, "@AccountId", DbType.Int64, debitId); //Normal Parameter
ObjDB.AddParameter(objdbCmd, "IsSuccess", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Default, null); //Output Parameter
ObjDB.ExecuteNonQuery(objdbCmd);
if (ObjDB.GetParameterValue(objdbCmd, "IsSuccess") != DBNull.Value)
retVal = Convert.ToInt32(ObjDB.GetParameterValue(objdbCmd, "IsSuccess").ToString());

Hope this will help you, if so request you to share the feedback and accept the answer.

To know how to pass the output parameter in Linq you can get the details from the below link.

https://stackoverflow.com/questions/3950494/getting-stored-procedure-output-parameter-with-linq-and-entity-framework`

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar6y ago
Accepted answer
As I can see your question is single but it is containing three questions in the part as bifurcated below:

1. How to use the output parameter in SQL Server.
2. How to use the try-catch in SQL Server and
3. How to call the procedure containing an output parameter.

Below are the solutions for that: Using an output parameter in SQL Server stored procedure and also using the try-catch in the stored procedure.
CREATE PROC usp_divide(
    @a decimal,
    @b decimal,
    @c decimal output
) AS
BEGIN
    BEGIN TRY
        SET @c = @a / @b;
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;
To call a SQL Server procedure which contains an ouptput parameter you need to create a variable of the desired data type as defined in the procedure and pass same as shown below.
declare @r decimal(18, 2)
exec usp_divide 50, 10, @r output

exec usp_divide 5, 0, @r output
Here the output of the first query will be 5 while the output of second query is as shown below.
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 usp_divide 8 Divide by zero error encountered.
Here there are already some of the functions available in SQL Server such as Error_number(), Error_Line(), etc.
If your output parameter is of the varchar i.e., string type then you can set it in cath block with concatenating above message such as
SET @c = 'The error is Error Number: ' + ERROR_NUMBER() + ', '+ 'Error Line: ' + ERROR_LINE() + 'Error Message' + Error_Message()
Now, to call a procedure that contains an output parameter you need to pass the values of the parameter in the below way.
In C#
objdbCmd = ObjDB.GetStoredProcCommand("YourProcedureName");
              ObjDB.AddInParameter(objdbCmd, "@AccountId", DbType.Int64, debitId); //Normal Parameter
              ObjDB.AddParameter(objdbCmd, "IsSuccess", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Default, null); //Output Parameter
              ObjDB.ExecuteNonQuery(objdbCmd);
              if (ObjDB.GetParameterValue(objdbCmd, "IsSuccess") != DBNull.Value)
                  retVal = Convert.ToInt32(ObjDB.GetParameterValue(objdbCmd, "IsSuccess").ToString());
Hope this will help you, if so request you to share the feedback and accept the answer.
To know how to pass the output parameter in Linq you can get the details from the below link.

https://stackoverflow.com/questions/3950494/getting-stored-procedure-output-parameter-with-linq-and-entity-framework`
Jignesh Kumar6y ago
Hey, Please find below link which will help you how to use out parameter in mvc application https://www.c-sharpcorner.com/article/stored-procedure-with-input-and-output-parameter-using-entity-framework-in-asp-m/
Rajanikant Hawaldar6y ago
Hi Chittaranjan watch this tutorial explained well https://m.youtube.com/watch?v=bldBshxuhMk
Ramchand Repalle6y ago
Hi Chittaranjan,

This below link might help.
https://blog.cloudboost.io/how-to-use-sql-output-parameters-in-stored-procedures-578e7c4ff188
Amit Mohanty6y ago
Hey Chitta check the below links

https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-try-catch/

https://www.mytecbits.com/microsoft/sql-server/return-error-message-stored-procedure