Chris, Jeff...

If I understand the OP correctly, they could not solve this problem with ISNULL or COALESCE alone because of the awkward data type requirement.

Example:

DECLARE

@x1 varchar(10) = NULL,

@x2 int = NULL;

SELECT

dbo.fnchkNull(@x1) AS col1,

dbo.fnchkNull(@x2) AS col2

Should return:

col1col2

No Data0

Scott:

If I understand the requirement correctly, your solution does not work if the data type of the variable fed to the function is an int and it's NULL.

Run these to see what I mean:

DECLARE @x1 int = NULL;

DECLARE @x2 int = 1;

SELECT

SQL_VARIANT_PROPERTY (@x1, 'BaseType') AS col1,

[dbo].[fnchkNull](@x1) AS col2;

SELECT

SQL_VARIANT_PROPERTY (@x2, 'BaseType') AS col1,

[dbo].[fnchkNull](@x2) AS col2;

Karthick,

I could not figure out a solution to what I believe your asking. To be clear you are asking that:

DECLARE @x int = NULL;

SELECT [dbo].[fnchkNull](@x);

Returns a 0

and:

DECLARE @x varchar(10) = NULL;

SELECT [dbo].[fnchkNull](@x);

Returns "No Data".

Is that correct?

Edit: formatting....