When I query a SQL Server 2008 system dynamic management view which is implemented as a table-valued function and it returns an empty result set, how can I tell that the reason for the empty result set is that an error occurs in the function, and then, what that error is?
3
-
Do you have a specific case causing an issue?JNK– JNK2011-02-22 21:26:46 +00:00Commented Feb 22, 2011 at 21:26
-
You can test this by raising errors within stored procedures (try with different error levels, to see the behavior with and without sql exceptions).Oded– Oded2011-02-22 21:27:45 +00:00Commented Feb 22, 2011 at 21:27
-
We might be able to say more if you list the function name and how you're using it. What are you trying to achieve?Sir Wobin– Sir Wobin2011-02-22 21:30:12 +00:00Commented Feb 22, 2011 at 21:30
Add a comment
|
2 Answers
There is a much more useful way to force an error inside a function in TSQL than performing a division by zero. What we do at our company is to cast a string (describing the very problem) and convert it to a string.
if @PersonID is null
insert into @Result values(@Right, cast('FT_AclGetAccess must never be called with @PersonID null' as int))
This will result in an error on the application server looking like this:
Conversion failed when converting the varchar value 'FT_AclGetAccess must never be called with @PersonID null' to data type int.
A little string manipulation on the application server and you get a pretty sane error message for the log file! ;-)
Comments
They don't. You cannot use THROW nor RAISERROR inside T-SQL functions. Some devs force a divide by 0 to trigger an error inside UDFs. This works fine, but sometimes confuses the poor soul that has to investigate a divide by 0 error that comes from apparently nowhere.
1 Comment
Jim Flood
It's funny that two diametrically opposed answers were both upvoted. For others looking, there is some more detail here: stackoverflow.com/questions/1485034/….