Persuading a SQL Server UDF to throw

There are ways to get SQL to explode from within a function, hacky and not pretty, but there are ways!

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]()
	DECLARE @IDOfUserMakingChange INT = (SELECT UserID_PK FROM dbo.[User] WHERE 'MYDOMAIN\' + LogonName = SUSER_NAME())
	IF (@IDOfUserMakingChange IS NOT NULL)
		RETURN @IDOfUserMakingChange
		RETURN CAST('User not found' AS INT)


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.

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment