doobya (7/7/2010)
Grant Fritchey (7/7/2010)
But if you have pre-existing procs with established and functioning logic, why are you changing them to use TRY/CATCH at all? I would only suggest rewriting them as needed and migrating them to TRY/CATCH, and yes, updating the logic then.TRY/CATCH does work differently than @@error and thank the gods that it does. For example, please show me how to catch a deadlock error and resubmit the query without using TRY/CATCH.
Why are people struggling to understand this simple post?
- I have an EXISTING stored procedure that WORKS FINE "dbo.spWorks"
- if I call this stored procedure "dbo.spWorks" from OUTSIDE a TRY block - it works OK
- if I call this stored procedure "dbo.spWorks" from INSIDE a TRY block - it does not work any more
I am NOT changing anything - just CALLING the SAME, UNCHANGED procedure from both INSIDE and OUTSIDE TRY blocks
Excuse the caps but I am getting frustrated when people are missing such a simple point
The trouble is that you are not understanding what people are saying - I think not understanding it at all. A stored procedure behaves in an environment, when you change the environment the behaviour changes. One part of the environment is the parameter values - I think you would be somewhat horrified if its behaviour didn't change when you changed the parameters. Another part of its environment is the schema it references: change that, and you will change the behavious - in fact the SP may go from doing something useful to falling about all over the place. Yet another part of its environment is the transaction isolation level - changing that can change the stored procedure from never causing a deadlock to causing frequent deadlocks. I could carry on listing components of the environment for a very long time, and all of them potentially change the behaviour of the stored procedure. but I think I've listed enough now. You can either accept that whether the stored procedure is invoked within a try block or not is one of those environmental factors that determine its behaviour, or you can refuse to recognise that simple fact - but if you continue to adopt the latter course you will never be able to cope with SQL or with any other programming language that has serious exception handling.
Tom