3

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?

Jim Flood
  • 8,144
  • 3
  • 36
  • 48
  • Do you have a specific case causing an issue? – JNK Feb 22 '11 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 Feb 22 '11 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 Feb 22 '11 at 21:30

2 Answers2

8

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! ;-)

Silverdust
  • 1,503
  • 14
  • 26
6

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    It's funny that two diametrically opposed answers were both upvoted. For others looking, there is some more detail here: http://stackoverflow.com/questions/1485034/how-to-report-an-error-from-a-sql-server-user-defined-function. – Jim Flood Mar 09 '11 at 07:13