Why SQL variable still keep previous value ?

Answer 1

Your second query doesn't return a row so it never gets assigned.

Make sure you "reset" the variable before each time you reuse it.

DECLARE @var int;
select @var = 3;

print @var;

select @var = 8 where 1=0; -- nothing happens

print @var;

Answer 2

DECLARE @FileExtensionID int

SELECT * FROM FileExtensions WHERE (Name= 'pdf')
SELECT @FileExtensionID = ID FROM FileExtensions WHERE (Name= 'pdf')
SELECT IsNULL( @FileExtensionID , 0) -- First Select

SET @FileExtensionID = NULL

SELECT * FROM FileExtensions WHERE (Name= '')
SELECT @FileExtensionID = ID FROM FileExtensions WHERE (Name= '')
SELECT IsNULL( @FileExtensionID , 0)-- Second Select

Your line SELECT @FileExtensionID = ID FROM FileExtensions WHERE (Name= '') will not overwrite @FileExtensionID if there are no results.

Answer 3

When you perform a SELECT @Veraible = SomeField FROM SomeTable..., essentially it executes for each record returned from the SELECT. So apparently the second SELECT (where Name = "") is returning no reocrds, so the @FileExensionID variable is never updated the second time around.

Answer 4

Because there is no matching row for criteria Name = '', the variable will not be written over & hence it has its old value, with it.

Answer 5

When a query returns no rows, the assignment @FileExtensionID = ID is not performed, which is why the variable still contains the value that was assigned earlier.