Saturday, January 27, 2007

SQL-equivalent to NVL2

I have been using T-SQL for as long as I can remember and one of the things I never found was an equivalent to Oracle's NVL2. Well, this time around I did not want to use a CASE statement or even the more involved COALESCE. I just needed a plain function that would evaluate if the given expression is null return this otherwise return this.

The following T-SQL function will provide the equivalent to NVL2, or at least something close enough. The trick is to use the sql_variant data type.

IF EXISTS (SELECT id
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[isnull2]')
AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
DROP FUNCTION [dbo].[isnull2]
END
GO
CREATE FUNCTION [dbo].[isnull2]
(
@expression sql_variant,
@return_if_null_value sql_variant,
@return_if_not_null_value sql_variant
)
RETURNS sql_variant
AS
BEGIN
DECLARE @return sql_variant;
IF (@expression IS NULL)
SET @return = @return_if_null_value
ELSE
SET @return = @return_if_not_null_value
RETURN @return
END

Please note that you will need to ensure you cast either the parameters you pass or the result to the desired type. This is specially true if you are calling the logic in a stored procedure or embedded query that is executed from a .NET or similar application via something like ADO.NET.

No comments: