CODEWITHSUNDEEP
sqlsql-server
weblorin
weblorin

Reputation: 358

Why does using a TSQL variable in a WHERE clause produce different results than using a constant of the same value?

When I run the following two SQL statements (SQL Server 2008 R2), I get different results. The first excludes rows where pernr is NULL; the second includes them. 

SELECT pernr FROM tbl
WHERE pernr <> 123;

DECLARE @input int = 123; 
SELECT pernr FROM tbl
WHERE pernr <> @input;

Why is this? What is going on here? I would have thought SQL Server would replace my variable with the value and evaluate these queries identically, but clearly not! I'm running these in SSMS.

Here is a repro script:

SET ANSI_NULLS OFF;

CREATE TABLE tbl (pernr INT NULL);
GO

INSERT INTO tbl (pernr)
VALUES (NULL)
    ,(123)
    ,(NULL)
    ,(456);



SELECT pernr FROM tbl
WHERE pernr <> 123;

DECLARE @input int = 123; 
SELECT pernr FROM tbl
WHERE pernr <> @input;

Results:

-------
456


-------
NULL
NULL
456

Upvotes: 4

Views: 165

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Oh Hai SAP

I guess it's because you have SET ANSI_NULLS OFF;

If you have SET ANSI_NULLS ON; you'll find they return the same thing

What does select sessionproperty('ANSI_NULLS') return?

Unfortunately this still doesn't explain why a parameterised select returns something different to a non parameterised one

Upvotes: 5

Related Questions