Update fails after IF EXISTS

6 Jan 2006 9:13 PM

Mike

I would like to know if anyone else has had the same experience where the
Update portion runs even though the IF EXISTS returns nothing. The sql with
the select works fine. Here is the sample script.

use tempdb
go
Create table test ( id int identity, createdate datetime default getdate())

insert into test
default values

select * from test

--This fails..
if exists(select * from syscolumns where id = object_id('test') and name =
'create_date')
Begin
Update Test
SET create_date = createdate
where createdate is not null
End

It even fails if I use a different IF statements:
if (select count(*) from syscolumns where id = object_id('test') and name =
'create_date')

--This works fine
if exists(select * from syscolumns where id = object_id('test') and name =
'create_date')
Begin
Select * from test
End

6 Jan 2006 9:28 PM

Jens

SQL Server checks if the table is present if the column exists, the
table doesnt contain such a column so the test will fail. Something
similar can be seen on deferred name resolution for compiling stored
procedures:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_5wa6.asp

One way would be the use of dynamic sql:

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens Suessmeyer.

6 Jan 2006 9:33 PM

Mike

I used dynamic sql for the update statement, but it took me by surprise that
the update clause was actually executing after the IF clause while the
select was not.

Thanks.

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1136582917.613081.98180@g44g2000cwa.googlegroups.com...
> SQL Server checks if the table is present if the column exists, the
> table doesnt contain such a column so the test will fail. Something
> similar can be seen on deferred name resolution for compiling stored
> procedures:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_5wa6.asp
>
> One way would be the use of dynamic sql:
>
> http://www.sommarskog.se/dynamic_sql.html
>
> HTH, Jens Suessmeyer.
>

6 Jan 2006 9:39 PM

Louis Davidson

I see, and yes, it does a syntax/object reference pass before it executes
the code. So all columns referenced in tables need to exist. However,
change the code to:

--This fails..
if exists(select * from syscolumns where id = object_id('test') and name =
'create_date')
Begin
Update Test2
SET create_date = createdate
where createdate is not null
End
go

And it will run. It has to do with late binding of object names versus the
way it deals with columns. It is annoying, but this is the way it works.
If you can do it securitywise you could use dynamic SQL, but it is best not
to do this kind of thing anyhow, especially since your query would update
any rows with a non null create date, which would be all of the rows, likely

:)

----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Mike" <y4forums.t.mdgo***@xoxy.net> wrote in message
news:OdBRQYwEGHA.2912@tk2msftngp13.phx.gbl...
>I would like to know if anyone else has had the same experience where the
>Update portion runs even though the IF EXISTS returns nothing. The sql with
>the select works fine. Here is the sample script.
>
> use tempdb
> go
> Create table test ( id int identity, createdate datetime default
> getdate())
>
> insert into test
> default values
>
> select * from test
>
> --This fails..
> if exists(select * from syscolumns where id = object_id('test') and name =
> 'create_date')
> Begin
> Update Test
> SET create_date = createdate
> where createdate is not null
> End
>
> It even fails if I use a different IF statements:
> if (select count(*) from syscolumns where id = object_id('test') and name
> = 'create_date')
>
>
> --This works fine
> if exists(select * from syscolumns where id = object_id('test') and name =
> 'create_date')
> Begin
> Select * from test
> End
>
>
>
>