T-SQL Tips: Can a UDF Return Values of Multiple Datatypes?

This was a question raised in one of the forums recently.
This blog explains how we can create a user defined function (UDF) which can return values of different datatypes under different conditions.
Consider the case where we require creating a user defined function which based on the conditions have to return values of different data types. Since a user defined function can have only a single datatype for the return value we need to find a datatype which can store values for different data types. There's a datatype available in SQLServer called sql_variant which can be used for this purpose

As per MSDN sql_variant is :-

A data type that stores values of various SQL Server-supported data types.

https://msdn.microsoft.com/en-us/library/ms173829.aspx

So we can utilize sql_variant datatype as the return value for the user defined function which will have the ability to store and return values for all the SQLServer supported datatypes

As an illustration we can consider a user defined function as below

ALTER FUNCTION dbo.TestVariant
(
@Mode char(1)
)
RETURNS sql_variant
AS
BEGIN
DECLARE @Ret sql_variant
IF @Mode = 'i'
SELECT @Ret = 1
ELSE IF @Mode = 'v'
SELECT @Ret = 'Test'
ELSE IF @Mode = 'd'
SELECT @Ret = '2015-06-17'
ELSE IF @Mode = 'n'
SELECT @Ret = N'സ്വാഗതം'
ELSE IF @Mode = 'u'
SELECT @Ret = '9DF38CBA-D314-42D8-9DF2-E4BC3108501C'
ELSE 
SELECT @Ret = NULL

RETURN (@Ret)
END

If you analyse the function you can see that UDF returns values of different datatypes based on the input value for @Mode parameter.

Now try executing the function

SELECT dbo.TestVariant('i')

returns

1

similarly see the output for other values of @Mode parameter

As seen from the output its evident that the data type of the value returned by UDF will change based on the input value. Thus we can make use of sql_variant datatype to make sure UDF returns values of various data types.