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

1. I am not able to get this function to work - any help would be appreciated
2. 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?

Answer 1

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

Answer 2

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