Adam Haines (5/3/2010)

---

I think it is also important to understand what is going on underneath the hood, when NULLIF is used. Unbeknownst to some, NULLIF is actually a case expression under the hood, which means it is subject to data type precedence. The second value is not always implicitly converted to the data type of the first value. Whichever side has the less data type precedence will be converted to the other data type. This can cause implict conversion errors, if the columns cannot be converted to the higher data type.

e.g.

DECLARE @t TABLE(fl int);

INSERT INTO @t VALUES (0);

DECLARE @var CHAR(1),
	@var2 DATETIME
SET @var = ''
SET @var2 = GETDATE()

select nullif(fl, @var)
FROM @t
--Compute Scalar(DEFINE[Expr1004]=CASE WHEN [fl]=CONVERT_IMPLICIT(int,[@var],0) THEN NULL ELSE [fl] END))

select nullif(fl, @var2) 
FROM @t
--Compute Scalar(DEFINE[Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[fl],0)=[@var2] THEN NULL ELSE [fl] END))

Paul White NZ (5/3/2010)

---

@sknox:
I laughed when I saw Hugo's reply.
Paul