Persuading a SQL Server UDF to throw
Unfortunately (and annoyingly, at times!) it's not possible to persuade a SQL Server User-Defined Function to throw, though I've managed to find (more by luck thank judgement!) a way to get the desired outcome.
Perform a cast that's not allowed
The short version of this is that if you have a scalar function that returns INT, having CAST('SOME STRING' AS INT) in a return path will result in:
Msg 245, Level 16, State 1, Line 32 Conversion failed when converting the varchar value 'User not found' to data type int.
As you can see from the error message I've pasted in, the function I'm writing is one to retrieve the ID of an application user, basically so that ad-hoc data change scripts don't have to have either a hard-coded User Id, tweaks made to them at the point of being run to have the Id changed to that of the person running it, or any other kind of nasty or inaccuracy. In total the function looks a lilttle like this:
CREATE FUNCTION [dbo].[GetUserIdForLoggedInUser]() RETURNS INT AS BEGIN DECLARE @IDOfUserMakingChange INT = (SELECT UserID_PK FROM dbo.[User] WHERE 'MYDOMAIN\' + LogonName = SUSER_NAME()) IF (@IDOfUserMakingChange IS NOT NULL) BEGIN RETURN @IDOfUserMakingChange END ELSE BEGIN RETURN CAST('User not found' AS INT) END END GO
And then invoked like this:
DECLARE @userID INT = [dbo].[GetUserIdForLoggedInUser]()
If the user doesn't exist in the [User] table, *boom*, which stops the rest of the script from being executed! Of course the better / proper way to do this would be to write it into a stored procedure and do things properly, but as a proof of concept of something you can do in a user-defined function, it is kinda neat.