Dynamic Return type in a function

Question

Post reply

Dynamic Return type in a function

karthik82.vk

SSCrazy

Points: 2002
More actions
March 3, 2015 at 11:34 pm

#398107

Hi,
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value.
Below is the function
GO
/****** Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnchkNull](@colname varchar(100))
RETURNS varchar(50)
AS
BEGIN
DECLARE @OutputString VARCHAR(255)
IF LEN(@colname) = 0
BEGIN
SET @OutputString = ISNULL(@colname,'No Data')
END
IF Len(@colname) <> 0
BEGIN
IF @colname = 'NULL'
BEGIN
SET @OutputString = 'No Data'
END
SET @OutputString = @colname
END
RETURN ISNULL(@OutputString, 'No Data')
END
GO
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'
I tried for this but not able to do it. Can anyone please help me?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Why do you need to create a function for this. simply do this for text fields

ISNULL(NULLIF(@column,'NULL'),'No Data'))

or this for Numeric fields

ISNULL(NULLIF(@column,'NULL'),0))

Its also likely to be more efficient than calling a scalar function.

Test Rig

DECLARE @test-2 varchar(10)='a'

Print ISNULL(NULLIF(@test ,'NULL'),'No Data')

Set @test-2 ='NULL'

Print ISNULL(NULLIF(@test ,'NULL'),'No Data')

Set @test-2 =NULL

Print ISNULL(NULLIF(@test ,'NULL'),'No Data')

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

karthik82.vk (3/3/2015)
Hi,
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value.
Below is the function
GO
/****** Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnchkNull](@colname varchar(100))
RETURNS varchar(50)
AS
BEGIN
DECLARE @OutputString VARCHAR(255)
IF LEN(@colname) = 0
BEGIN
SET @OutputString = ISNULL(@colname,'No Data')
END
IF Len(@colname) <> 0
BEGIN
IF @colname = 'NULL'
BEGIN
SET @OutputString = 'No Data'
END
SET @OutputString = @colname
END
RETURN ISNULL(@OutputString, 'No Data')
END
GO
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'
I tried for this but not able to do it. Can anyone please help me?

This is plain silly. It offers nothing over ISNULL and will slow your queries down.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

In order to accept and return differing data types, you'd have to use sql_variant, which will affect how easily you can use the value returned by the function:

CREATE FUNCTION [dbo].[fnchkNull] (

@colname sql_variant

)

RETURNS sql_variant

AS

BEGIN

RETURN (

SELECT CASE

WHEN CAST(SQL_VARIANT_PROPERTY ( @colname , 'BaseType' ) AS varchar(30)) LIKE '%int%'

THEN ISNULL(@colname, 0)

WHEN @colname IS NULL OR @colname = '' OR @colname = 'NULL'

THEN 'No Data'

ELSE @colname

END

)

END

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

I have to agree with Chris. Use ISNULL or COALESCE instead. The code will be a lot faster than using a scalar function this way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

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....

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Alan.B (3/5/2015)
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.

Let's review the requirement as the OP stated it...

The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'

Gosh, the folks writing the code should know if the column they're working with is an INT or a VARCHAR. It they don't there may be bigger problems at hand.

If they know it's an INT, then ISNULL(INTcolumn,0) will work just fine.

If they know it's a VARCHAR, then ISNULL(NULLIF(VCcolumn,''),'No Data') will work just fine and also handles blanks.

And the fact that SQL_VARIANT returns a NULL for the datatype if it's passed a NULL is an even stronger reason to not try to do this by as a UDF.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

Jeff Moden (3/5/2015)
Alan.B (3/5/2015)
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.
Let's review the requirement as the OP stated it...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'
Gosh, the folks writing the code should know if the column they're working with is an INT or a VARCHAR. It they don't there may be bigger problems at hand.
If they know it's an INT, then ISNULL(INTcolumn,0) will work just fine.
If they know it's a VARCHAR, then ISNULL(NULLIF(VCcolumn,''),'No Data') will work just fine and also handles blanks.
And the fact that SQL_VARIANT returns a NULL for the datatype if it's passed a NULL is an even stronger reason to not try to do this by as a UDF.

Agreed.

This is a bazaar requirement.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001