Thursday, February 6, 2020

Var Assignment SELECT Gotcha

I got stumped by something I haven't seen in awhile. Question - what's the value of @objid at the end of this?

DECLARE @objid int = -1
SELECT @objid = [OBJECT_ID]
FROM sys.objects WHERE 1=0
SELECT @objid

I coded a script with the assumption that it would be null if the WHERE condition is not met, the same way a column would be null. But instead, it kept its original value.

To fix:

DECLARE @objid int = -1
SELECT @objid = CASE WHEN 1=0 THEN [OBJECT_ID] END
FROM sys.objects
SELECT @objid


This will provide the results we wanted.

No comments:

Post a Comment