CODEWITHSUNDEEP
sql-serversql-server-2008
Jim Flood
Jim Flood

Reputation: 8457

How do SQL Server table-valued functions report errors?

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?

Upvotes: 3

Views: 3770

Answers (2)

Silverdust
Silverdust

Reputation: 1493

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

Upvotes: 9

Remus Rusanu
Remus Rusanu

Reputation: 294187

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.

Upvotes: 6

Related Questions