Regarding Sql error code

Question

I have a small question in my mind.

Suppose I make a big stored procedure in sql server and call this sp from c# application using ado.net.
if sp returns any error or exception now i want to get the actual error with error code in application end.

Please suggest how to do ??

Thanks in advance.

Munesh Sharma · Answer

http://stackoverflow.com/questions/3990288/catch-the-error-from-stored-procedure-in-c-sharp

Anupam Singh · Answer

There is not any standard but you can categorize errors by error code. You can get error codes in SYS.MESSAGES system view (in master db). and catch it using try { // your coe } catch(SqlException ex) { Console.WriteLine(ex.ErrorCode); }

Ravi Shekhar · Answer

Hi There are multiple types of error and exception we caught in our application. Is there any built in function in Visual studio to get a proper msg for that error or exceptions which user understand easily. I can make this type of function but no. of errors and exceptions are very huge. Then how ?? Ravi

Ravi Shekhar · Answer

Yeah you are right anupam sir... m also checking the sqlexception automatically gets the error msg from sp

Anupam Singh · Answer

Hi Ravi Actually its no need to write any additional code SqlException class will handle it for you.

Khan Abrar Ahmed · Answer

Hi when you are writing the sproc then use below code and and the logic when you have insert update or delete logic then use transaction other wise you can remove the tag. if any error happen then it will throw the actual error and in the c# code when you are calling the sproc then use try catch block then you will get the error message there which is in sproc. BEGIN TRY BEGIN TRANSACTION --Place the Insert update logice --- COMMIT TRANSACTION END TRY BEGIN CATCH --Roll back the transaction ROLLBACK TRANSACTION --If any error the raise the error DECLARE @ErrorMessage NVARCHAR ( 4000 ); DECLARE @ErrorSeverity INT ; DECLARE @ErrorState INT ; SELECT @ErrorMessage = ERROR_MESSAGE () @ErrorSeverity = ERROR_SEVERITY () @ErrorState = ERROR_STATE (); RAISERROR ( @ErrorMessage  @ErrorSeverity  @ErrorState ); END CATCH