Reputation: 34054
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
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 NULL
s, generally.
Upvotes: 2
Reputation: 24144
I guess you can use sql_variant. Also what about [dbo].[IsNullOrWhiteSpace]
? Does it takes a String
type?
Upvotes: 3