jeudi 25 mai 2017

IF condition being checked in SQL even if it doesn't match

I have 2 if conditions in my code. If it falls under condition number 1, it executes a select from a certain view and that's ok. However, if it falls under condition number 2, it selects from another view, which is broken (although it exists), because it references columns from a table that doesn't exist anymore.

My intention is to not bother fixing the view (or dropping it), since i have a logic that manipulates a variable to make it fall under the condition which references the working view.

However, it seems that SQL validates all the views in the code, even if it is inside a IF block which is never executed, generating the error:

Msg 207, Level 16, State 1, Procedure vtest_table, Line 21
Invalid column name 'name'.
Msg 4413, Level 16, State 1, Line 32
Could not use view or function 'vtest_table' because of binding errors.

Example:

create database test

create table test_table (
id int identity(1,1),
name varchar(20)
)
go

create view vtest_table
as 
select id, name
from test_table
go

-- breaking the view
alter table test_table
drop column name
go

declare @var int
set @var = 2
if (@var = 2)     -- it should fall under this condition and execute this block
begin
print 'test'
end

-- however, the view in the select statement in this block is checked, and as the view is broken, it returns the error.
else if (@var = 1)
begin
select * from vtest_table
end

Aucun commentaire:

Enregistrer un commentaire