October 9, 2013 at 8:22 am
Nice question. I knew the answer because I tried to use try/catch inside an UDF a few months ago. 🙂
October 9, 2013 at 8:45 am
Good question, thanks! 🙂
October 9, 2013 at 8:47 am
Narud (10/9/2013)
Nice question. I knew the answer because I tried to use try/catch inside an UDF a few months ago. 🙂
"Hey, I've got an idea! Oh... no I don't." 🙂
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
October 9, 2013 at 8:56 am
L' Eomot Inversé (10/9/2013)
Nice question.I've never understood why try...catch was banned in functions, but the explanation for this question provides a clue. I'd never seen the error messages, and their text is that clue.
These error messages mentioned in the explanation seem a bit odd. Of course the classical definition of a function is that it has no side effects, and if the intention is to enforce that then excluding things like insert, delete, and update statements against anything other than local table variables is a reasonable (indeed essential) decision, as are various other exclusions; but claiming that "begin try" or "begin catch" is a side-effecting operation seems somewhat strange. Off the top of my head I can see no imaginable way in which these statements can effect the state of any database data or metadata (the statements inside the try or catch block could, but inside a function the try or catch block can't contain any such statement simply because there they would be inside the function body, and are themselves banned quite apart frm the try or catch) so I have to say that it appears to me that the wording chosen for those error messages is misleading claptrap.
It would be nice to know what the real reason for excluding those statement is, and whether it has any validity or is just silliness, because if it is just silliness we might be able to persuade MS to allow this very useful error detection and containment construct inside multi-statement functions.
I too would be interesting in a better explanation of the reason for the prohibition. Also, I'm a bit surprised at the number of incorrect answers. Suppose it's due to people also not being able to think of a good reason why it should be prohibited.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
October 9, 2013 at 10:28 am
Nice and easy one..
Thanks..
October 9, 2013 at 11:14 am
Does this explanation from the MSDN forums answer the question?
I am not sure I understand the connection. Maybe it would not even get to the CATCH because the whole function just quits?
October 9, 2013 at 11:19 am
timwell (10/9/2013)
Does this explanation from the MSDN forums answer the question?
"Transact-SQL errors [...] are treated differently inside a function. In functions, such errors cause the execution of the function to stop."
So functions don't support error-handling because functions don't support error-handling. Got it.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
October 9, 2013 at 11:22 am
timwell (10/9/2013)
Does this explanation from the MSDN forums answer the question?I am not sure I understand the connection. Maybe it would not even get to the CATCH because the whole function just quits?
It looks as if that's what it's saying. If so, it means that those error messages really are nonsense.
Tom
October 9, 2013 at 1:16 pm
VERY easy. I too am surprised how many people got it wrong.
Thanks, Sasidhar!
October 9, 2013 at 2:20 pm
L' Eomot Inversé (10/9/2013)
It would be nice to know what the real reason for excluding those statement is, and whether it has any validity or is just silliness, because if it is just silliness we might be able to persuade MS to allow this very useful error detection and containment construct inside multi-statement functions.
No idea about the official reason, but here's why I don't care.
There are three types of UDFs in SQL Server.
* Scalar functions - unless used with utmost care and full awareness of how they work and how they cripple the optimizer, they should not be used - they can truly kill your performance.
* Multi-statement table-valued functions - unless used with utmost care and full awareness of how they work and how they cripple the optimizer and feed it with way off estimates on cardinality, they should not be used - they can kill your performance even more.
* Inline table-vallueud functions - should in my opinion have been called parametrized views, because they behave like views. Don't cripple the optimizer, generally perform quite well. Only down side - they can only be one single query, nothing else.
Allowing TRY CATCH blocks would only effect the types of functions that you probably shouldn't be using anyway,
October 9, 2013 at 4:59 pm
Hugo Kornelis (10/9/2013)
L' Eomot Inversé (10/9/2013)
It would be nice to know what the real reason for excluding those statement is, and whether it has any validity or is just silliness, because if it is just silliness we might be able to persuade MS to allow this very useful error detection and containment construct inside multi-statement functions.No idea about the official reason, but here's why I don't care.
...
...
Allowing TRY CATCH blocks would only effect the types of functions that you probably shouldn't be using anyway,
I guess that is based on the view that the only use for UDFs is in a query.
However, they could also be used in much the same way as stored procedures, and should be just as efficient as stored procedures (but not, unless written as a single statement, as efficient as an inline tabled-valued UDF) and if it wasn't for this restriction they would be quite useful in that respect; if I wanted something to be free of side effects I could write it as a UDF instead of as a stored procedure, or, perhaps more realistic since I trust myself not to write stuff with side effects when I don't intend them, I could instruct a junior programmer to do it as a UDF and not as an SP. In addition, if UDF's were always used in place of SPs whose function is simply to return a result (scalar or rowset) without causing any side effects I would be able to see immediately which program units were available for use when the intention was to use a declarative model of programming for some task, rather than having to look at the code to find out.
However, I recognise that I'm in a minority of people concerned with databases in having the sort of background that makes me take an interest in declarative and fiunctional styles and want to be able to detect easily exactly which bodies of code are side-effect-free and which are not. So even if UDFs were allowed to do error management they probably wouldn't be exploited for being known to be side-effect free, and that (at least until database developers stop thinking in terms of purely state-oriented procedural programming using sets and start thinking instead about functional and declarative programming using sets, which I don't see happening any time soon) means that it wouldn't be worth-while for MS to allow try-catch in functions if it requires significant development to allow an error to transfer control to the catch block instead of exiting the function (which seems to be be the reason why TRY is not allowed in functions, despite the text of the error messages, if the thing timwell found is accurate).
So although you don't care, my background ensures that I do.
Tom
October 10, 2013 at 9:55 am
Got it right but still do not like that try catch cannot be used. If I could only catch an error and log it to a table with the appropriate message, condition and stack I might be more aware of what is going on in real operation. I do this in other code, why not in the execution of UDF's?
Not all gray hairs are Dinosaurs!
October 10, 2013 at 9:56 am
ronmoses (10/9/2013)
timwell (10/9/2013)
Does this explanation from the MSDN forums answer the question?"Transact-SQL errors [...] are treated differently inside a function. In functions, such errors cause the execution of the function to stop."
So functions don't support error-handling because functions don't support error-handling. Got it.
ron
Exactly my reaction.
October 10, 2013 at 6:51 pm
I got this wrong, probably because I never even tried using try/cach in a function. In fact, I try to avoid UDFs altogether as they quite often screw up the execution plan.
October 11, 2013 at 2:53 am
easy one... thanks
Manik
You cannot get to the top by sitting on your bottom.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply