Creating a function with a common type

I use this syntax in different 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 does this, but with a genric type. This is what I'm looking for

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 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 use this in an 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)
+3
source share
2 answers

I think you can use sql_variant . Also what about [dbo].[IsNullOrWhiteSpace]? Is type required String?

+3
source

There is an old-skool type sql_variant:

A data type that stores the values ​​of the various data types supported by SQL Server.

, (, varchar(max), geography ..) .


, , , COALESCE:

COALESCE(@Name,Name)

, COALESCE NULLIF:

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

, NULL s, .

+2

All Articles