Create loosely typed udf similar to ISNULL

Is it possible to create a udf where the return type is the same as the first argument.

Other notes:

• The first argument can be any type and if it is null the return value is null with no underlying type.
• Constraints on the second argu开发者_开发技巧ment so the type matches that of the first.

http://msdn.microsoft.com/en-us/library/aa933210(v=SQL.80).aspx

You could try using the sql_variant datatype for a UDF. (I've haven't BTW)

However, a simple in-line ISNULL use the datatype of the 1st argument anyway.

Unless you haven't given us full information in the question about what you intend to achieve...

Edit: after update

The whole point of ISNULL is to replace a NULL (1st argument) with a value (2nd argument).

There is no "type" of NULL. An int or a varchar can take a NULL value. But it's still the base datatype

ISNULL constrains 2nd argument to the 1st type anyway: the 2nd argument must be implicitly convertible. So SELECT ISNULL(CAST(NULL AS int), 'foo') will fail

So, use in-line ISNULL which does what you want. A udf is not needed

Edit, after comment

You can't have one udf to cover all datatypes.

Not least, a udf has a single return datatype.

SQL Server's ISNULL(), as you point out, takes 2 params, the first being an expression of any type, and the second being the replacement if the first arg is null. It returns the same type as the arg being checked.

check_expression : Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value : expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expression.

Return Types: Returns the same type as check_expression.

It sounds from your edited question as if ISNULL already does what you're after.