How to Use an UPDATE within IF ELSE SQL Server

Question

I’m trying to verify if a column exists within a certain table.

When checking if it exists, I want to update the column with the value 1, if not, create and update.

However, every time I run the error occurs that the column does not yet exist.

In the execution cycle, he first considers my update to alter table.

To illustrate what I’m trying to do:

IF EXISTS( SELECT * FROM BANCO_DE_DADOS.INFORMATION_SCHEMA.COLUMNS 
            WHERE (TABLE_NAME) = 'MinhaTabela' 
            AND  (COLUMN_NAME) = 'CodZona'  )  

BEGIN
update BANCO_DE_DADOS..MinhaTabela set CodZona = 1 where CodZona < 1 or CodZona is null

END

ELSE

BEGIN 

ALTER TABLE BANCO_DE_DADOS..MinhaTabela
ADD  [CodZona] [int] NULL

update BANCO_DE_DADOS..MinhaTabela set CodZona = 1

END

Error:

Mensagem 207, Nível 16, Estado 1, Linha 7
Nome de coluna 'CodZona' inválido.
Mensagem 207, Nível 16, Estado 1, Linha 7
Nome de coluna 'CodZona' inválido.

Answer 1

After the ALTER TABLE it won’t work to realize the UPDATE, follows an alternative that can help you, as in my example below:

IF OBJECT_ID('TempDB.dbo.#MinhaTabela') IS NOT NULL 
drop table #MinhaTabela
go 
create table #MinhaTabela (id int) 

insert into #MinhaTabela values(1)
go 

select * from #MinhaTabela go 

IF NOT EXISTS(SELECT TOP 1 1 FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE table_name like '%MinhaTabela%' AND COLUMN_NAME = 'Cod')
BEGIN
ALTER TABLE #MinhaTabela ADD Cod int;
END

select * from #MinhaTabela go

exec sp_executesql N'update a set a.Cod = 1 from #MinhaTabela a where (a.Cod < 1 or a.Cod is null);'

select * from #MinhaTabela go

Answer 2

Place ALTER TABLE as dynamic SQL in the ELSE block.

EXEC sp_executeSQl N'ALTER TABLE BANCO_DE_DADOS..MinhaTabela ADD  [CodZona] [int] NULL'