SQL Case compare null value against null to equal a pass

Transact-SQL
1

Blockquote

I have to compare two values if if the same then display pass.
Even if they are NULL if two values equal NULL then this is a pass.

I cannot see how to do this.

Blockquote
-- Create table t1 and insert values.
drop table dbo.t1
CREATE TABLE dbo.t1 (a INT NULL, b INT NULL)
INSERT INTO dbo.t1 (a,b)
select (NULL), (NULL)

SELECT * FROM t1

GO

-- Print message and perform SELECT statements.
PRINT 'Testing default setting';
DECLARE @varname int;
SET @varname = NULL;

SELECT a
FROM t1
WHERE a = @varname;

SELECT a
FROM t1
WHERE a <> @varname;

SELECT a
FROM t1
WHERE a IS NULL;
GO

select a, b,
case when a = b
then 'pass'
else
'failed'
end
from t1

PRINT 'Testing ANSI_NULLS ON';
SET ANSI_NULLS ON;
GO
DECLARE @varname int;
SET @varname = NULL

SELECT a
FROM t1
WHERE a = @varname;

SELECT a
FROM t1
WHERE a <> @varname;

SELECT a
FROM t1
WHERE a IS NULL;

select a, b,
case when a = b
then 'pass'
else
'failed'
end
from t1

GO

PRINT 'Testing ANSI_NULLS OFF';
SET ANSI_NULLS OFF;
GO
DECLARE @varname int;
SET @varname = NULL;
SELECT a
FROM t1
WHERE a = @varname;

SELECT a
FROM t1
WHERE a <> @varname;

SELECT a
FROM t1
WHERE a IS NULL;
GO

select a, b,
case when a = b
then 'pass'
else
'failed'
end
from t1

-- Drop table t1.
DROP TABLE dbo.t1;

Blockquote

2

case when a = b or (a is null and b is null)
then 'pass'
else
'failed'
end

1 Like
3

Super - perfect - thanks so much

4

Message removed.... I misread Scott's code (probably due to caffeine deprivation) and read it as the case was in the WHERE clause for some reason.

1 Like
5

A CASE within the SELECT will not cause any performance issues :grinning:. Sometimes it can cause issues in WHERE and JOIN clauses.

6

Oh, my... my apologies... not enough coffee. I don't know why I read that as being in the WHERE clause. Thanks for the correction, Scott.