SQL Server doesn't care wrong column in a subquery

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 2008 Forums

Transact-SQL (2008)

SQL Server doesn't care wrong column in a subquery

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-10-28 : 05:38:17

How to validate invalid column in subquery in script.
SQL Server does not care wrong column within a subquery
What i have a full script for all of my stored procedures and function.
In some stored procedures there are some inner/sub queries which have invalid column name but there is no error in the script when i run it or Parse it.IS tehre anyway i can find the error in my script without going in each of Stored procedures/Functions

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Sachin.Nand

2937 Posts

Posted - 2010-10-28 : 09:49:21

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150951

PBUH

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-10-28 : 16:03:25

I have a bit help from
[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a2cdebe7-4c91-4ec7-9f36-3fc530f614b1/?prof=required[/url]
One problem in the mentioned solution is that it is not handling Table Value Data Types

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-28 : 16:51:11

Qualify all of your column names. It's most likely not that SQL is ignoring invalid column names, it's that you don't understand the scoping rules for subqueries.

This is perfectly valid
DECLARE @t1 TABLE (
col1 INT
)

DECLARE @t2 TABLE (
col2 INT
)

SELECT col1 FROM @t1 t1 WHERE col1 IN (SELECT col1 FROM @t2 AS t2)

It's valid because all columns in the outer query are visible within the subquery. Now if you qualify the column names as follows, it will give the expected error when parsed or executed, because there is no col1 in @t2

DECLARE @t1 TABLE (
col1 INT
)

DECLARE @t2 TABLE (
col2 INT
)

SELECT t1.col1 FROM @t1 t1 WHERE t1.col1 IN (SELECT t2.col1 FROM @t2 AS t2)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources