Handling NULL values while setting a value to a variable

Question

I have tried the queries below to check whether the current year is Leap-Year or not.

Declare @leapyearstrt int = 0

--1st query
select @leapyearstrt = ISNULL(ISLEAPYEAR,0)
                       FROM [dbo].[TimeElement] 
                       WHERE CurrentDate = getdate()

--2nd query
SET @leapyearstrt  = (SELECT isnull(ISLEAPYEAR,0) 
                       FROM [dbo].[TimeElement] 
                       WHERE CurrentDate = getdate()
                       )

If the data does not exist in the table for the given date, 1st query returns @leapyearstrt as 0, but 2nd query returns @leapyearstrt as NULL.

Why does it happen? How to assign the 0 value in the SET statement if data does not exist in the table?

(P.S : I have used SET to assign the value because select may return more than one row)

If there is no value then Parameter have to return the default value assign to it . is it? — bmsqldev, Dec 03 '15 at 07:56
Possible duplicate of [SET versus SELECT when assigning variables?](http://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables) — Lukasz Szozda, Dec 03 '15 at 08:00

score 1 · Accepted Answer · answered Dec 03 '15 at 11:00

First of all, if SELECT returns more than 1 row, then SET (your second query) fails:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

But, variable assignment within SELECT (your first query) works. It is not defined which row will be used to assign the final value, though.

If you want to use SET, then make sure that SELECT returns at most 1 row:

SET @leapyearstrt  = (SELECT TOP(1) isnull(ISLEAPYEAR,0) 
                       FROM [dbo].[TimeElement] 
                       WHERE CurrentDate = getdate()
        -- ideally you should add a proper ORDER BY to pick the row that you need
                       );

And then add this line of code to cover the case when SELECT returns 0 rows.

SET @leapyearstrt = ISNULL(@leapyearstrt, 0);

Giorgi Nakeuri · Answer 2 · 2015-12-03T11:02:03.937

0

You can use ISNULL function, assuming the subquery is returning one row:

SET @leapyearend  = ISNULL((SELECT TOP 1 ISLEAPYEAR 
                            FROM [dbo].[TimeElement] 
                            WHERE CurrentDate = getdate()
                           ), 0)

The difference between those two is that in the first query if row is not found the assignment is not executed at all. In second version it is executed wether row is found or not.

edited Dec 03 '15 at 11:02

answered Dec 03 '15 at 08:01

Giorgi Nakeuri

35,155
8
47
75

2

What if subquery returns more than one row? – Lukasz Szozda Dec 03 '15 at 08:02
@lad2025, correct comment, added correction, but from names of table and columns I guess it shouldn't return more then one row. – Giorgi Nakeuri Dec 03 '15 at 08:03
1

OP says: `P.S : I have used SET to assign the value because select may return more than one row` – Lukasz Szozda Dec 03 '15 at 08:12
Is it safe to use Select instead of SET if query returning more than one row. I think SELECT will return top 1 row if multiple rows are there – bmsqldev Dec 03 '15 at 08:55

Handling NULL values while setting a value to a variable

2 Answers2