How to use out parameter in SQL server stored procedure

Question

Hi all, How to use error message as out parameter in SQL server stored procedure with generic ExecuteNonQuery method. Also used in asp.net core MVC or asp.net MVC application.

Suraj Kumar · 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`

Amit Mohanty · Answer

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

Jignesh Kumar · Answer

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 Hawaldar · Answer

Hi Chittaranjan watch this tutorial explained well https://m.youtube.com/watch?v=bldBshxuhMk

Ramchand Repalle · Answer

Hi Chittaranjan, This below link might help. https://blog.cloudboost.io/how-to-use-sql-output-parameters-in-stored-procedures-578e7c4ff188

How to use out parameter in SQL server stored procedure

Answers (5)