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

I have 2 conditions in my code. If it meets condition number 1, it performs a selection from a certain kind, and that's okay. However, if it meets condition number 2, it chooses from a different kind that is split (although it does exist) because it refers to columns from a table that no longer exists.

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

However, it looks like SQL checks all views in the code, even if it is inside an IF block that is never executed, generating an 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 refer to a view that doesn't exist at all, say:

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

      

it performs as expected. SQL seems to only check dependencies if the view exists.

+3


source to share


2 answers


update your view because the column name column is from the table



alter view vtest_table
as 
select id
from test_table

      

+1


source


Well SQL is a declarative language, not an imperative, so ... I would just remove the view reference all together or wrap it in a block TRY / CATCH

.



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

      

+1


source







All Articles