March 3, 2015 at 11:34 pm
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?
March 4, 2015 at 3:36 am
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
March 4, 2015 at 4:44 am
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.
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
March 4, 2015 at 3:28 pm
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.
March 4, 2015 at 8:05 pm
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
Change is inevitable... Change for the better is not.
March 5, 2015 at 1:06 pm
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....
-- Itzik Ben-Gan 2001
March 5, 2015 at 4:11 pm
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
Change is inevitable... Change for the better is not.
March 5, 2015 at 6:10 pm
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.
-- Itzik Ben-Gan 2001
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply