Behavior in sql scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Behavior in sql scripts

Author

Topic

willcs18
Starting Member

2 Posts

Posted - 2004-05-24 : 18:45:09

I'm building scripts to make period database updates for an application I'm about to release. I'm encountering some annoying behavior. Here is a simplified example:

This causes an error


Set @AccountCnt = (select count(*) from syscolumns where id = object_id('AutoNotify') AND Name='Account')

IF (@AccountCnt > 0)
BEGIN

   select Account from AutoNotify

END

while this does't...


Set @AccountCnt = (select count(*) from syscolumns where id = object_id('AutoNotify') AND Name='Account')

IF (@AccountCnt > 0)
BEGIN

   select Account from NonExistentTable

END

This example isn't actually doing anything, but it's just for demonstration. Basically, if the 'Account' column doesn't exist within the AutoNotify table, the select statement should not be called. If the column doesn't exist, the first statement errors out as if it's trying to validate the select statement beforehand. However, in the second statement, no error is tossed unless the 'Account' column exists (which is what SHOULD happen). It's strange... the condition works properly is the contained select statement is completely erroneous. But if it's only partially erroneous (column is missing, but table exists), it complains.

Any ideas? :-

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-24 : 18:51:19

Dynamic SQL might get around the validation.
Basically, build up a string that is "select Account from NonExistentTable" and execute that.

Do some reading from the articles on this page:
http://www.sqlteam.com/searchresults.asp?SearchTerms=dynamic+sql

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

willcs18
Starting Member

2 Posts

Posted - 2004-05-24 : 19:32:30

Hi Michael,

Thanks for the response. I was able to solve my problem here. Although, one thing to note is that you cannot use dynamic sql if your destination is a table variable.


	insert @tblTemp 
	Exec ('select pk_AutoNotify, Account, Template from ' + 'AutoNotify')

... will error out. I changed my script to create and drop a temporary table, which worked fine with dynamic sql. Thanks a bunch =]

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-24 : 19:39:56

NP! Thanks for posting your solution!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources