Re: IF Statement causes Invalid Column Name

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 15:46:50 +0200

This has to do with parse and compile time versus execute time. And also deferred (delayed) name resolution.
If you refer to a column which doesn't exist, SQL Server will not be able to parse your SQL, so you will never
get to the execute stage.

So, why does it work for a table? Answer is deferred name resolution. If the table doesn't exists, SQL Server
tries to resolve the table name at execution time. In other words, we have deferred name resolution for table
names, but not for column names.

If you want to ensure that tables, columns etc exists, do a check against the INFORMATION_SCHEMA views, the
system table or use the system functions (like OBJECT_ID()). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Donmn" <anonymous@discussions.microsoft.com> wrote in message
news:C7DE5EEB-1918-4FB7-B9FA-32E35D91289F@microsoft.com...
> Hi
>
> Thanks in advance for any help and please note I am relatively new to SQL.
>
> I am supporting an update script running in the Analyzer.  I am having issues with an IF statement:
>
> When an IF statement references a missing column within, it produces an "Invalid Column Name".  But, surely
this should be validated when the IF statement equates to TRUE.
>
> On the other hand when the IF statement refers to a table which doesn't exist it has no problem.  I have
isolated the code for review.  Any help would be appreciated and an answer would be excellent.
>
> Kind Regards
> Donmn
>
> /*tblTMP.lngMISSINGCOL does not exist and tblTMP.tmpID = 1 Returns: Invalid Column Name*/
> IF ((SELECT [tmpID] FROM [dbo].[tblTMP]) = 0)
>     BEGIN
>         PRINT 'IF True'
>         SELECT tblTMP.lngMISSINGCOL FROM tblTMP
>     END
> ELSE
>     PRINT 'IF False'
> GO
>
> /*tblMISSINGTABLE does not exist and tblTMP.tmpID = 1 Returns: IF False, as expected*/
> IF ((SELECT [tmpID] FROM [dbo].[tblTMP]) = 0)
>     BEGIN
>         PRINT 'IF True'
>         SELECT tblMISSINGTABLE.lngMISSINGCOL FROM tblMISSINGTABLE
>     END
> ELSE
>     PRINT 'IF False'
> GO
>

Relevant Pages

  • Re: IF Statement causes Invalid Column Name
    ... This has to do with parse and compile time versus execute time. ... And also deferred name resolution. ... SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting proper error message at compile time if a table doesn
    ... Defferred name resolution is a very useful thing, ... Pro SQL Server 2000 Database Design - ... >>> When I compile this stored procedure: ... >>> I am running large scripts and I must get proper error message at ...
    (microsoft.public.sqlserver.programming)
  • Re: Different connection string syntax between CF and regular Framework?
    ... Resolution to this was ... configuration is to right click on the SQL Server Group in Enterprise ... Manager and look under Network Configuration for TCPIP. ... > something to do with network connectivity. ...
    (microsoft.public.dotnet.framework.compactframework)
  • RE: How to check stored proc dependency
    ... This is not possible in sql server 2000 because of the deferred name ... You can create a procedure that references an object that has not ... See "Deferred Name Resolution and Compilation" in BOL. ... > I tried sp_depends but it does not return correct information. ...
    (microsoft.public.sqlserver.programming)
  • Re: Data Source = problem
    ... It looks like a problem with name resolution as the page with the IP works ... Though it could be a workaround (they don't share all the same connection ... -- "James" a écrit dans le message de ... > Long story short, it happened about 15 minutes ago and one of the pages that> connects to SQL Server worked, but none of the other ones would. ...
    (microsoft.public.inetserver.asp.general)