[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SQL compile error not trapped

Bijesh

3/9/2007 4:24:00 PM

Hi All,

I am facing a wierd problem condition where SQL compiler is not trapping
compile/run time error.
Here is an example to illustrate:
Create Table Table1
(
Col1 INT
)
Go
Create Table Table2
(
Coll1 INT --Note an extra "L" in the column name.
)
Go
Now fire this query:
Select * from Table1 Where Col1 IN (Select Col1 From Table2)
--(0 row(s) affected)
Though column "Col1" does not exist in Table2, it does not throw any error.
But when we fire the following query, it does throw error
Select * from Table1 Where Col1 IN (Select xyz From Table2)
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'xyz'.

Observation:
It is noted that when the column name in "Where" condition of Table1 is used
inside the subquery(Table2) it does not throw error. But when some other name
such as 'xyz' is used in subquery, it throws error.

Also, this error is not trapped when compiling SP.

Can someone help explain this to me? I am using SQL Server 2000 Enterprise
edition.

Thanks in advance.
Bijesh
2 Answers

Tibor Karaszi

3/9/2007 4:40:00 PM

0

This is expected behavior. What you are doing is a correlated subquery, i.e., the same as:

Select * from Table1 Where Col1 IN (Select Table1.Col1 From Table2)

What you want to get a habit of doing is to always qualify the columns with the table name/alias as
soon as a query refers to more than one table, and you would get the expected error:

Select * from Table1 AS t1 Where Col1 IN (Select t2.Col1 From Table2 AS t2)


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Bijesh" <Bijesh@discussions.microsoft.com> wrote in message
news:2D00AACD-E997-4FEA-9C4C-881C9ABC9A39@microsoft.com...
> Hi All,
>
> I am facing a wierd problem condition where SQL compiler is not trapping
> compile/run time error.
> Here is an example to illustrate:
> Create Table Table1
> (
> Col1 INT
> )
> Go
> Create Table Table2
> (
> Coll1 INT --Note an extra "L" in the column name.
> )
> Go
> Now fire this query:
> Select * from Table1 Where Col1 IN (Select Col1 From Table2)
> --(0 row(s) affected)
> Though column "Col1" does not exist in Table2, it does not throw any error.
> But when we fire the following query, it does throw error
> Select * from Table1 Where Col1 IN (Select xyz From Table2)
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'xyz'.
>
> Observation:
> It is noted that when the column name in "Where" condition of Table1 is used
> inside the subquery(Table2) it does not throw error. But when some other name
> such as 'xyz' is used in subquery, it throws error.
>
> Also, this error is not trapped when compiling SP.
>
> Can someone help explain this to me? I am using SQL Server 2000 Enterprise
> edition.
>
> Thanks in advance.
> Bijesh

Bijesh

3/9/2007 7:56:00 PM

0

Thanks Tibor,
Your solution helped me learn something new in SQL server.

Thanks,
Bijesh

"Tibor Karaszi" wrote:

> This is expected behavior. What you are doing is a correlated subquery, i.e., the same as:
>
> Select * from Table1 Where Col1 IN (Select Table1.Col1 From Table2)
>
> What you want to get a habit of doing is to always qualify the columns with the table name/alias as
> soon as a query refers to more than one table, and you would get the expected error:
>
> Select * from Table1 AS t1 Where Col1 IN (Select t2.Col1 From Table2 AS t2)
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "Bijesh" <Bijesh@discussions.microsoft.com> wrote in message
> news:2D00AACD-E997-4FEA-9C4C-881C9ABC9A39@microsoft.com...
> > Hi All,
> >
> > I am facing a wierd problem condition where SQL compiler is not trapping
> > compile/run time error.
> > Here is an example to illustrate:
> > Create Table Table1
> > (
> > Col1 INT
> > )
> > Go
> > Create Table Table2
> > (
> > Coll1 INT --Note an extra "L" in the column name.
> > )
> > Go
> > Now fire this query:
> > Select * from Table1 Where Col1 IN (Select Col1 From Table2)
> > --(0 row(s) affected)
> > Though column "Col1" does not exist in Table2, it does not throw any error.
> > But when we fire the following query, it does throw error
> > Select * from Table1 Where Col1 IN (Select xyz From Table2)
> > Server: Msg 207, Level 16, State 3, Line 1
> > Invalid column name 'xyz'.
> >
> > Observation:
> > It is noted that when the column name in "Where" condition of Table1 is used
> > inside the subquery(Table2) it does not throw error. But when some other name
> > such as 'xyz' is used in subquery, it throws error.
> >
> > Also, this error is not trapped when compiling SP.
> >
> > Can someone help explain this to me? I am using SQL Server 2000 Enterprise
> > edition.
> >
> > Thanks in advance.
> > Bijesh
>
>