I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )
Open a connection
Begin the transaction
Execute the command
In the Stored Proc
do multiple operations one by one
stop processing further
Throw the error
rollback the transaction
commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
The problem appears to be in the way that you are handling the error in the stored proc. When you execute a sql command through an object that you've instantiated through your code. You will only get an exception back in the code if the error level of the sql server is high enough. A good example of this would be if you were to set it up to intentionally violate a primary key constraint when executing your statement.
From the way that you have phrased your question it seems that you are defining an "error" as some condition in the behavior of the statement or the data that is unacceptable with what you are trying to do. Either way I would argue that attempting to throw an exception from your SQL Server is likely an inefficient way to handle the condition that you detect in your stored proc.
A much more common way to handle this type of situation is to pass in an OUT parameter to your stored proc that you can use to monitor the state of the activity you're performing. If for whatever reason you do need to handle the situation this way please submit the section of the T-SQL you're using to raise the error. Nothing below a level of 10 will come close to raising an exception in your application.
John V. McCarthy
Ok..I am executing dymanic sql statements and calling different stored procedures. If any one of the operations fails..i need to track what gone wrong and roll back the transaction and pass the error message to C# for trouble shooting.
Can you please let me know how exactly can we acheive this?
In order to catch the SQL Exception that is thrown you would need to implement something like this.
static void Main(string args)
SqlConnection myConn = new SqlConnection("Data Source=myDataSourceName;Initial Catalog=myInitialCatalog;Persist Security Info=True;User ID=myID;Password=myPassword");
SqlCommand myCommand = new SqlCommand("sp_TestException", myConn);
myCommand.CommandType = CommandType.StoredProcedure;
catch (System.Exception ex)
John V. McCarthy
I have done that using the RAISERROR function in stored procedures and recieved custom messages in C# for the errors occured. So all it would take is to create an error handler for each sql statement in your stored procedure by checking the @@ERROR value after each.