SQL Server Functions Passed into SQL

Question

Help on this would be appreciated.

Scenario: I want to create a scenario to handle nulls with specific default values. The table below will have the default values:

CREATE TABLE dbo.NullhandleMetadata
(
ColumnName varchar(50)
, Defaultvalue varchar(50)
, DataType (varchar(50)
)

The table definition:

ColumnName  Defaultvalue    Dataype
 columnA    Unknown         varchar(50)
 ColumnB    999             int

I want to use a function to be called from within the SELECT statements to inject the defalut value if the column is null. The function is as below:

CREATE FUNCTION dbo.Default_handle (@AttValue varchar(200)) RETURNS varchar(200)

BEGIN
DECLARE @value varchar(200)
    SELECT 
        @value= Defaultvalue
    FROM 
        dbo.NullhandleMetadata
    WHERE ColumnName = @AttValue

  RETURN @value
END

Below is an example of the SELECT statement with the the function:

SELECT          
    ISNULL(columnA, dbo.Default_handle(@value)) AS ColumnAData

FROM source_table

I am not able to get this function to work - any help would be appreciated
Is there a way for the same function to deal with different data types and the return value to also return a different data type? So if the column is integer then it returns and integer or if it is a string/varchar field then to return a varchar?

"not able to get this function to work" -- what's that supposed to mean? — mustaccio
– mustaccio, Commented Sep 28, 2017 at 17:12
So you have a table to hold the default value for every table and column if the value is null? This sounds horrific. And keep in mind that scalar functions are horribly inefficient. Are you going to be wrapping every single column in your select statements with ISNULL(MyColumn.Value, Default_handle('MyColumn')). Something sounds very wrong with this concept. — Sean Lange
– Sean Lange, Commented Sep 28, 2017 at 17:15
Columns don't have defaults unless you create default constraints on them. So, seems like a far easier and reliable thing to do would be to create default constraints, using the value you want to use. Update any existing null values and then alter the column definition to NOT NULL. — Jason A. Long
– Jason A. Long, Commented Sep 28, 2017 at 17:34

Jason A. Long · Accepted Answer · 2017-09-28 20:07:01Z

See what you think of this...

USE tempdb;
GO

CREATE TABLE dbo.TestingTable (
    id INT NOT NULL IDENTITY(1,1),
    Col1 DATETIME,
    Col2 VARCHAR(10),
    Col3 INT,
    Col4 DATE,
    Col5 TIME(0)
);
INSERT dbo.TestingTable (Col1, Col2, Col3, Col4, Col5) VALUES 
    (GETDATE(), NULL,    NULL, '2017-11-01', NULL),
    (NULL,      NULL,    NULL, '2017-11-01', '12:54:32'),
    (NULL,      NULL,    NULL, '2017-11-01', '12:54:32'),
    (NULL,      'Hello', NULL, NULL,         '12:54:32'),
    (NULL,      'Hello', NULL, NULL,         '12:54:32'),
    (GETDATE(), 'Hello', NULL, NULL,         '12:54:32'),
    (GETDATE(), 'Hello', 2587, NULL,         NULL),
    (GETDATE(), 'Hello', 2588, NULL,         NULL),
    (GETDATE(), 'Hello', 2589, '2017-11-01', NULL),
    (GETDATE(), NULL,    2590, '2017-11-01', NULL),
    (GETDATE(), NULL,    NULL, '2017-11-01', NULL);

-- create the dbo.NullhandleMetadata and use sys.columns to capture the object_id & column_ids.
USE tempdb;
GO

CREATE TABLE dbo.NullhandleMetadata (
    ObjectID INT,
    ColumnName sysname,
    ColumnID INT,
    Defaultvalue VARCHAR(50)
    );

INSERT dbo.NullhandleMetadata (ObjectID, ColumnName, ColumnID, Defaultvalue)
SELECT 
    c.object_id,
    c.name,
    c.column_id,
    Defaultvalue = CASE  c.name
                        WHEN 'id  ' THEN '0'
                        WHEN 'Col1' THEN 'GETDATE()'
                        WHEN 'Col2' THEN ''''''
                        WHEN 'Col3' THEN '0'
                        WHEN 'Col4' THEN 'GETDATE()'
                        WHEN 'Col5' THEN  '00:00:00'
                    END
FROM
    sys.columns c
WHERE 
    c.object_id = OBJECT_ID('tempdb.dbo.TestingTable', 'U');

SELECT * FROM dbo.NullhandleMetadata;

-- create the function dbo.tfn_NullhandleMetadata (see function comments)
CREATE FUNCTION dbo.tfn_NullhandleMetadata
/* ====================================================
Pulls default values from dbo.NullhandleMetadata
parameters will accept either the object_id & column_id as INT values 
or as the actual names for easier use.
==================================================== */
(
    @ObjectID sysname,
    @Column sysname
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
    SELECT 
        nm.Defaultvalue
    FROM
        dbo.NullhandleMetadata nm
    WHERE 
        nm.ObjectID IN (OBJECT_ID(@ObjectID, 'U'), TRY_CAST(@ObjectID AS INT))
        AND (
                nm.ColumnName = @Column
                OR 
                nm.ColumnID = TRY_CAST(@Column AS INT)
            );
GO

Here are two different use cases for the function. Check the execution plans of both before choosing which syntax to use...

-- Use case 1...
-- cleaner syntax but slightly higher plan cost.
UPDATE tt SET 
    tt.Col3 = nm.Defaultvalue
FROM
    dbo.TestingTable tt
    CROSS APPLY dbo.tfn_NullhandleMetadata('tempdb.dbo.TestingTable', 'col3') nm
WHERE 
    tt.Col3 IS NULL;

-- Use case 2...
-- a bit more cumbersome but lower plan cost.
DECLARE @Defailt VARCHAR(50);
SELECT 
    @Defailt = nm.Defaultvalue
FROM
    dbo.tfn_NullhandleMetadata('tempdb.dbo.TestingTable', 'col3') nm;

UPDATE tt SET 
    tt.Col3 = @Defailt
FROM
    dbo.TestingTable tt
WHERE 
    tt.Col3 IS NULL;

Results of running against Col3...

id          Col1                    Col2       Col3        Col4       Col5
----------- ----------------------- ---------- ----------- ---------- ----------------
1           2017-09-28 14:52:39.280 NULL       0           2017-11-01 NULL
2           NULL                    NULL       0           2017-11-01 12:54:32
3           NULL                    NULL       0           2017-11-01 12:54:32
4           NULL                    Hello      0           NULL       12:54:32
5           NULL                    Hello      0           NULL       12:54:32
6           2017-09-28 14:52:39.280 Hello      0           NULL       12:54:32
7           2017-09-28 14:52:39.280 Hello      2587        NULL       NULL
8           2017-09-28 14:52:39.280 Hello      2588        NULL       NULL
9           2017-09-28 14:52:39.280 Hello      2589        2017-11-01 NULL
10          2017-09-28 14:52:39.280 NULL       2590        2017-11-01 NULL
11          2017-09-28 14:52:39.280 NULL       0           2017-11-01 NULL

A Long. - your solution was so much better but they want it to be driven from the metadata table and not from system tables :(
@Aarion - That solution isn't actually dependent on the system tables. You'll notice that the function itself does't look at sys.columns at all. I simply "borrowed" the object_id & column_id as a matter of convenience and to help guarantee uniqueness and accuracy... It also provides the convenience of using the OBJECT_ID() function allowing search by name or id.

Stéphane CLEMENT · Accepted Answer · 2017-09-28 18:55:50Z

0

Maybe this will work :

   SELECT ISNULL(columnA, A.Defaultvalue) AS ColumnAData
       , ISNULL(columnB, B.Defaultvalue) AS ColumnBData
       , ISNULL(columnC, C.Defaultvalue) AS ColumnCData
    FROM source_table AS S
    LEFT JOIN dbo.NullhandleMetadata AS A ON (A.ColumnName = 'columnA')
    LEFT JOIN dbo.NullhandleMetadata AS B ON (B.ColumnName = 'columnB')
    LEFT JOIN dbo.NullhandleMetadata AS C ON (C.ColumnName = 'columnC')

It could still be an issue with implict conversions...

answered Sep 28, 2017 at 18:55

Stéphane CLEMENT

3721 silver badge14 bronze badges

1 Comment

Aarion Over a year ago

Thank you for this, but I need the select statement IS NULL condition when true populated via the SQL Function 'dbo.Default_handle'

Collectives™ on Stack Overflow

SQL Server Functions Passed into SQL

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related