CODEWITHSUNDEEP
sqlsql-server
Amr Badawy
Amr Badawy

Reputation: 7673

Why SQL variable still keep previous value?

I notice a strange thing during doing some stored procedures
I can explain that by the following example :

DECLARE @FileExtensionID int

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

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

in the above queries i have a table called "FileExtensions" with ID ,Name columns and i try to set ID in variable @FileExtensionID, the problem that in the first select -SELECT IsNULL(@FileExtensionID,0)- @FileExtensionID have a vaild ID - for exmaple 9 -but in the second one when no row returned from 

SELECT @FileExtensionID = ID FROM FileExtensions WHERE (Name= '')

it also still keep previous one - 9 - and not return 0
i don't know why it happen so please could any tell me how it come ?

Upvotes: 2

Views: 737

Answers (5)

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120997

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.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

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

Upvotes: 0

Mike Mooney
Mike Mooney

Reputation: 11989

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.

Upvotes: 0

Codesleuth
Codesleuth

Reputation: 10541

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.

Upvotes: 0

cairnz
cairnz

Reputation: 3957

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;

Upvotes: 4

Related Questions