Can I declare a local variable not null?

Question

In T-SQL, I declare a local variable for use with some query like so:

DECLARE @var_last datetime;
SET @var_last = (SELECT TOP(1) col_date FROM tbl_dates ORDER BY col_date);

In an application I'm testing, it would be an error for this query to return NULL, and it's desirable for the query to return a crash error if it were.

I'd like to set @var_last to be NOT NULL but the syntax...

DECLARE @var_last datetime NOT NULL;

...is invalid. I can write a simple check on the return of the query to see if it's NULL, and error if it is, but my question is, is it not possible to declare a local variable as NOT NULL?

Answer 1

That's right, according the documentation for the DECLARE @local_variable, available at: http://technet.microsoft.com/en-us/library/ms188927.aspx, it doesn't accept a NULL | NOT NULL parameter -- those are only valid for column definitions.

If you want to stop execution if you return a NULL, then test for NULL and, if it is, RAISERROR; see: http://technet.microsoft.com/en-us/library/ms178592.aspx.

Answer 2

You can do something like this ...

Using ISNULL()

SELECT TOP(1) @var_last = ISNULL(col_date,'19000101') --<-- Some default value
FROM tbl_dates 
ORDER BY col_date;

Using COALESCE()

SELECT TOP(1) @var_last = COALESCE(col_date,'19000101') --<-- Some default value
FROM tbl_dates 
ORDER BY col_date;