Something that you should know when you are setting the value into a variable. The assignment (i.e., "=" operator) might not work as your expectation. So, try out the script below in the query window:
--declare a table variable
declare @t table (i int)
declare @ii int
-- append 2 record.
insert @t
select 1
union all
select 2
-- show all the records
select *
from @t
-- get the record that match '1'. The expected value in @ii is "1".
select @ii= i
from @t
where i = 1
-- Now, try to get the record that match '0'.
select @ii= i
from @t
where i = 0 --<< no record is '0'.
select @ii --<< this value is not NULL??!! What is this value??
When "i = 0", there is no record in @t table variable and "@ii = i" will not be executed. This means the "null" value will not be set to @ii. If you want to get the null value for @ii, then, you should do this:
select @ii = (select i from @t where i = 0)
Try it out and see the difference.
