CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 34054

Create a Function with Generic Type

I am using this syntax at various places,

CASE WHEN [dbo].[IsNullOrWhiteSpace](@MyColumn) = 1 OR [dbo].[IsNullOrWhiteSpace](Name) = 0 THEN [Name] ELSE @MyColumn END

The only change is Name and @MyColumn. But MyColumn can be any type. Any way to create a function that do this stuff but with genric type. This is what I am looking,

ALTER FUNCTION [dbo].[GetParameterOrPreviousValue]
(
    @Value1 AnyType
    ,@Value2 AnyType
)
RETURNS AnyType
AS
BEGIN

    RETURN CASE WHEN [dbo].[IsNullOrWhiteSpace](@Value1) = 1 OR [dbo].[IsNullOrWhiteSpace](@Value2) = 0 THEN @Value2 ELSE @Value1 END;

END

See the AnyType.

Update

Here is my IsNullOrEmpty,

ALTER FUNCTION [dbo].[IsNullOrWhiteSpace]
                (
                    @Value nvarchar(MAX)
                )
                RETURNS bit
                AS
                BEGIN

                    RETURN CASE WHEN LTRIM(RTRIM(ISNULL(@Value,''))) = '' THEN 1 ELSE 0 END;

                END

and I am using this in a UPDATE statement,

UPDATE T SET    [Name] = dbo.GetParameterOrPreviousValue(@Name, Name) 
                ,[Gender] = dbo.GetParameterOrPreviousValue(@Gender, Gender)
        ,[Location] = dbo.GetParameterOrPreviousValue(@Location, Location)
        ,[RawData] = dbo.GetParameterOrPreviousValue(@RawData, RawData)

Upvotes: 3

Views: 2976

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

There's the "old-skool" sql_variant type:

A data type that stores values of various SQL Server-supported data types.

But notice that most of the modern data types (e.g. varchar(max), geography, etc) aren't supported.

If all you want to do is ignore null parameter values, you can use COALESCE:

COALESCE(@Name,Name)

If you want to ignore null and empty parameter values, you can use COALESCE and NULLIF:

COALESCE(NULLIF(@Name,''),Name)

I'd resist the urge to treat all manner of whitespace the same as NULLs, generally.

Upvotes: 2

valex
valex

Reputation: 24144

I guess you can use sql_variant. Also what about [dbo].[IsNullOrWhiteSpace]? Does it takes a String type?

Upvotes: 3

Related Questions