SQL COALESCE Function Example
T-SQL developers sometimes require to test if a group of sql parameter values are NULL or not.
SQL developers can use the SQL COALESCE function which returns the first NOT NULL value of the function arguments.
T-SQL Coalesce function takes arguments not only sql variables but also table column values too.
So you can change the following sql script and SQL COALESCE function example for testing NULL values of a sql table row.
declare @var1 bigint
declare @var2 binary
declare @var3 bit
declare @var4 char
declare @var5 date
declare @var6 datetime
declare @var7 datetime2
declare @var8 datetimeoffset
declare @var9 decimal
declare @var10 float
declare @var11 geography
declare @var12 geometry
declare @var13 hierarchyid
--declare @var14 image
declare @var15 int
declare @var16 money
declare @var17 nchar
--declare @var18 ntext
declare @var19 numeric
declare @var20 nvarchar
declare @var21 real
declare @var22 smalldatetime
declare @var23 smallint
declare @var24 smallmoney
declare @var25 sql_variant
declare @var26 sysname
--declare @var27 text
declare @var28 time
declare @var29 timestamp
declare @var30 tinyint
declare @var31 uniqueidentifier
declare @var32 varbinary
declare @var33 varchar
declare @var34 xml
select coalesce(
CAST(@var1 as nvarchar(max)),
CAST(@var2 as nvarchar(max)),
CAST(@var3 as nvarchar(max)),
CAST(@var4 as nvarchar(max)),
CAST(@var5 as nvarchar(max)),
CAST(@var6 as nvarchar(max)),
CAST(@var7 as nvarchar(max)),
CAST(@var8 as nvarchar(max)),
CAST(@var9 as nvarchar(max)),
CAST(@var10 as nvarchar(max)),
CAST(@var11 as nvarchar(max)),
CAST(@var12 as nvarchar(max)),
CAST(@var13 as nvarchar(max)),
-- @var14,
CAST(@var15 as nvarchar(max)),
CAST(@var16 as nvarchar(max)),
CAST(@var17 as nvarchar(max)),
-- @var18,
CAST(@var19 as nvarchar(max)),
CAST(@var20 as nvarchar(max)),
CAST(@var21 as nvarchar(max)),
CAST(@var22 as nvarchar(max)),
CAST(@var23 as nvarchar(max)),
CAST(@var24 as nvarchar(max)),
CAST(@var25 as nvarchar(max)),
CAST(@var26 as nvarchar(max)),
-- @var27,
CAST(@var28 as nvarchar(max)),
CAST(CAST(@var29 as int) as nvarchar(max)),
CAST(@var30 as nvarchar(max)),
CAST(@var31 as nvarchar(max)),
CAST(@var32 as nvarchar(max)),
CAST(@var33 as nvarchar(max)),
CAST(@var34 as nvarchar(max))
)
If the output of the above sql Coalesce Select statement is NULL, then this means that all variables that are arguments of the sql coalesce function are NULL.