Friday, April 29, 2016

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

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.