sql server - Using EXISTS return wrong values in Script via Management Studio -

i have problem following queries.

part 1

updates table.

part 2

changes column-name.

if rerun script via management studio error,

the column "permissiontype_id" not exist.

the select statement in exists shows right result...

i such errors statements in script , need rid of them...

first statement

if exists (     select 1     sys.columns     name = n'permissiontype_id'         , object_id = object_id(n'[dbo].[gptbl_user_permission]')     ) begin update gptbl_user_permission set permissiontype_id = 1 objecttype_id = 'resultinfo' end go 

second statement

if exists (     select *     sys.columns     name = n'permissiontype_id'         , object_id = object_id(n'[dbo].[gptbl_user_permission]')     ) begin alter table [dbo].[gptbl_user_permission]  drop constraint pk_gptbl_user_permission  exec sp_rename '[dbo].[gptbl_user_permission].[permissiontype_id]'     , 'permissiontype'     , 'column';  alter table [dbo].[gptbl_user_permission] add constraint pk_gptbl_user_permission primary key clustered (     [objecttype_id] asc     , [permissiontype] asc     , [user_id] asc     )     (             pad_index = off             , statistics_norecompute = off             , ignore_dup_key = off             , allow_row_locks = on             , allow_page_locks = on             ) end go 

this error compile time error , not because of exists. if run exists query after second run, not return anything

select 1 sys.columns name = n'permissiontype_id'     , object_id = object_id(n'[dbo].[gptbl_user_permission]') 

so @ runtime, control never reach update after first run.

update gptbl_user_permission set permissiontype_id = 1 objecttype_id = 'resultinfo' 

the reason error because after initial run, sql server checks syntax of query, table , columns involved in query. @ point sql server knows there no column permissiontype_id , raises error.

solution

since error due compilation, can use dynamic sql force compilation occur @ run time update this:

if exists (     select 1     sys.columns     name = n'permissiontype_id'         , object_id = object_id(n'[dbo].[gptbl_user_permission]')     ) begin exec sp_executesql n' update gptbl_user_permission set permissiontype_id = 1 objecttype_id = ''resultinfo''' end go 

since exists return false every time after first run, hence dynamic update statement never compiled , won't error.