sql server 2008 - SQL select into a variable, resulting in incorrect value of variable -

the problem having select seeming use old value of variable, when values don't exist.

the first run of script, neither group id in table, both select statements on own should return answergroupid = null. subsequent runs of script both groups should exist , queries return valid int, used delete before re-creating.

i have workaround, understand going wrong prevent errors in future.

the basic structure of tables is: answer table has list of answers (one row per answer) linked via group id (int column). answergroup table has group level properties (groupid normal int column, table has separate primary key)

using sql server 2008.

--comments cut out irrelevant code  declare @groupnametoadd varchar(100) declare @answergroupid int  set @groupnametoadd = 'group1' print @groupnametoadd -- prints 'group1'  select @answergroupid = a.groupid myschema.answers join myschema.answergroup ag on a.groupid = ag.groupid a.answervalue = @groupnametoadd  -- ** result of above query null first time -- ** result of above query 1234 subsequent times  -- if (@answergroupid not null, delete existing entries) -- add new entry set @answergroupid = max(groupid)+1 myschema.answergroup  -- ** result of above query 1234 if re-run here**  set @groupnametoadd = 'group2' print @groupnametoadd -- prints 'group2'  select @answergroupid = a.groupid myschema.answers join myschema.answergroup ag on a.groupid = ag.groupid a.answervalue = @groupnametoadd  -- ** here selects 1234 first time (as if using old value of @groupnametoadd) when should null -- ** subsequent times selects 1235 (correctly)  -- if (@answergroupid not null, delete existing entries) -- add new entry includes line: 

due incorrect selection, query deletes newly inserted first entry.

the workaround:

before second selection statement

set @answergroupid = null 

here guess. if no rows returned query, no assignment happens. so, following returns "1":

declare @v int = 1;  select @v = 2 (select 10 c) t c = 2;  select @v; 

in second query, setting @groupnametoadd after row has been inserted. presumably, new name not in tables used query. so, there no matches query @answergroupid not reassigned.