SQL Server User Defined Function taking arguments of any type like ISNULL

Question

I need to create a user defined function that would operate similarly to ISNULL system function in the respect that it would accept arguments of any type and would return the value of the same type. How is this done?

Using SQL_VARIANT as a datatype would not work as SQL_VARIANT values require explicit casting. ISNULL does the data type conversions implicitly.

How to declare the generic types that do not require explicit casting?

Let me make it clear. I am not looking to replicate the functionality of ISNULL function. I used ISNULL function as a pattern of a function that takes two arguments of any data type supported by SQL Server and returns the value of the same data type as the arguments.

There are other SQL Server functions that implement the same pattern: accept arguments which data type is not explicitly declared and to return the value of some other data type, which is also not explicitly declared at function definition. The examples are: NULLIF, CAST, CONVERT.

I am wondering how this pattern could be accomplished, because any UDF requires explicit definition of arguments and return value data types.

What should be the replace value for each type? Replace a NULL-string with an empty string is rather obvious, but what is the non-value for datetime? Is Zero really the non-value of a numeric type? What do you want to achieve? — Shnugo, Oct 07 '15 at 18:40
T-SQL does not have generics and user-defined function return types are static (and required), so what you are literally asking for is impossible. There are several things I can think of that could sort-of achieve what you want (like a stored procedure that yields a table, or dynamic SQL) but also good reasons why you wouldn't want to use these in production code. Since `ISNULL` already exists and works just fine, what exactly do you want that is "similar" to `ISNULL`, and what for? — Jeroen Mostert, Oct 07 '15 at 20:36
Shnugo, the point is to have a function that is agnostic of the argument type and is capable of taking any, the way functions like ISNULL or NULLIF do. The point is not to implement another version of ISNULL, but to be able to create a new function, which would be able to handle the arguments of any time, and what is more important return the value of the same type as the arguments. — Ramzay, Jan 03 '16 at 07:47
Jeroen Mostert, I am puzzled, what is not clear in my original question. I clearly stated that I do not need to create another ISNULL function. I explain that similarity I am looking for is strictly pattern-wise. — Ramzay, Jan 03 '16 at 07:53

score 0 · Answer 1 · edited May 23 '17 at 12:15

I just answered a question which is somehow related: https://stackoverflow.com/a/32985478/5089204

There is no secure way to deal with empty values. ISNULL is very good in most cases, but won't compile if you do not have both parameters of an implicitly convertable type.

Look how this is treated in XML. Just paste this into an empty query window and execute:

--The NULL-element is not there at all
SELECT 'text' AS filled
      ,'' AS empty
      ,NULL AS NotThere
FOR XML PATH('row');   

--The NULL-element is rendered using "nil"
SELECT 'text' AS filled
      ,'' AS empty
      ,NULL AS NotThere
FOR XML PATH('row'),ELEMENTS XSINIL    

--Look at this: Both columns are called "TheName". They are implicitly concatenated
SELECT 'a' AS TheName
      ,'b' AS TheName
FOR XML PATH('row')

--That leads to: Concatenate nothing with an empty string will at least return the empty string.
--this is other/better than ISNULL, because it will work with any type...
SELECT NULL AS TheName
      ,'' AS TheName
FOR XML PATH('row')

--now an example with table data
DECLARE @tbl TABLE(int1 INT, int2 INT);
INSERT INTO @tbl VALUES(NULL,1); --first value is null

--int1 is swallowed
SELECT *
FROM @tbl
FOR XML PATH('row')

--both elements are there
SELECT int1, '' AS int1 --ISNULL(int1,'') would not compile here...
      ,int2, '' AS int2
FROM @tbl
FOR XML PATH('row')

--This is the result of the last example: Look what happens to the empty int element, its Zero! 
--This is the way this is handled in XML conversions. Try it with other types (like 'date' [result is 1900-01-01] or any other type)...
DECLARE @a XML='<row><int1></int1><int2>1</int2></row>';
SELECT a.b.value('int1[1]','int') AS int1
      ,a.b.value('int2[1]','int') AS int2
FROM @a.nodes('/row') a(b)

This is truly fascinating and very informative. I appreciate you sharing this information. I am sure I would find a good use for it. However, it brings me no closer to the answer of my question: how SQL Server implements functions like ISNULL, CONVERT, CAST, NULLIF without parameters and return values data types being explicitly defined? — Ramzay, Jan 03 '16 at 07:57

SQL Server User Defined Function taking arguments of any type like ISNULL

1 Answers1