CODEWITHSUNDEEP
sqlsql-serverif-statementview
arcoiro
arcoiro

Reputation: 31

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

Worth noting: if I reference a view that doesn't exist at all, say:

else if (@var = 1)
begin
select * from viewthatdoesntexist
end

it executes properly. It seems SQL only checks for dependencies if the view exist.

Upvotes: 3

Views: 191

Answers (2)

S3S
S3S

Reputation: 25142

Well SQL is a declarative language not imperative so there's that... I'd just remove the reference to the view all together or wrap this in a TRY / CATCH block.

begin try
    exec('select * from vtest_table')
end try
begin catch
    print 'your try failed'
end catch

Upvotes: 1

NIts577
NIts577

Reputation: 420

update your view because your drop name column from table

alter view vtest_table
as 
select id
from test_table

Upvotes: 1

Related Questions