CODEWITHSUNDEEP
sqlsql-servert-sql
nomen
nomen

Reputation: 3725

T-SQL: conditionally setting a variable

What is the correct syntax to set a T-SQL variable conditionally, from within a select statement?

I have code like the following:

select @CreationDate = dm.TS_UP
     , @RegenerateDocument = dm.REGENERATEBLOB
     , @CalculateRelativeToDate = case
                                    when dm.REGENERATEBLOB is null then getdate()
                                    when dm.REGENERATEBLOB = 'Y' then getdate()
                                    when dm.REGENERATEBLOB = 'N' then coalesce(@ApprovalDate, getdate())
                                  end
  from doc_mgmt dm
 where dm.ID_DOC = '11123'
   and dm.ID_PRMRY_TYPE = @CID
   and dm.ID_WRK_TYPE = @IID

Notice that none of the branches in the case end on a null.

But I am having some trouble with the @CalculateRelativeToDate variable, which sometimes comes in as null. I don't understand why that would be. In fact, in the case I am testing, the @RegenerateDocument flag is set to Y, so I think I SHOULD be getting getdate(). But I get null.

What is the correct syntax to set a T-SQL variable conditionally, from within a select statement?

Upvotes: 1

Views: 245

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If you want to ensure that the values are set, you can use aggregation. An aggregation query with no group by always returns exactly one row:

select @CreationDate = max(dm.TS_UP),
       @RegenerateDocument = max(dm.REGENERATEBLOB),
       @CalculateRelativeToDate = (case when max(dm.REGENERATEBLOB) is null then getdate()
                                        when max(dm.REGENERATEBLOB) = 'Y' then getdate()
                                        when max(dm.REGENERATEBLOB) = 'N' then coalesce(@ApprovalDate, getdate())
                                   end)
from doc_mgmt dm
where dm.ID_DOC = '11123' and
      dm.ID_PRMRY_TYPE = @CID and
      dm.ID_WRK_TYPE = @IID;

If you generally expect one row, there is no issue. The max() will return the value from that row. If no rows match, then the max() values will be NULL -- which seems to be what you expect.

Upvotes: 2

Related Questions