I'm a missing something or is the script "NZ function in SQL Server" just a two-parm version of the built-in COALESCE function?
Printable View
I'm a missing something or is the script "NZ function in SQL Server" just a two-parm version of the built-in COALESCE function?
NZ is closer to the IsNull() function in T-SQL than to Coalesce(). IsNull() is a two parameter function.
Coalesce is a multiple parameter version of the IsNull function. You can give it any amount of options, and it returns the first one that isn't null (or null if all parameters are null).
The main differnece between NZ and the SQL Server equivalents is that IsNull() and Coalesce() both require that the values be the same data type or at least convertable to each other's data types.
For example, this will give you an error in T-SQL because a string can not be converted to an integer:
Select IsNull('A String', 0)
MAK posted a user defined function for SQL Server that exactly mimics the functionality of NZ somewhere.
Here is MAK's function:
NZ function