January 14, 2008 at 12:41 pm
The subquery that select a column from the outer table is cumbersome and confusing in terms of semantics. It always has more concise and clear way for this type of queries.
As much as I'd like to agree with you, there are times when you might need to reference data from the outer query in the SELECT part of the inner query, like, for example in some bizarre calculation.
I'm not saying it's smart, or the right way to do it. But all the parser might be able to do (at best) MIGHT be some kind of "you realize this is a REALLY DUMB thing to run" kind of warning. There are way too many things you might be able to do that might look "dumb" or ludicrous to a parser which might STILL need to be run (because the rules themselves are ludicrous), or that might just happen to work given some very specific circumstance you the developer happen to know about.
For what it's worth, ANSI's standard mentions putting prefixes on ALL columns. Period. Nevermind if there are more than one table involved or not.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2008 at 4:19 pm
This issue is not new to SQL Server 2005 or to views, this problem exists in SQL Server 2000 (not sure in previoius versions). This does not happen all the time, not sure why this happens. Try this,
Create Table table1 (c1 int, c2 int)
Create Table table2 (c3 int, c4 int)
Select *
from table1
where C1 in ( select C1 from table2)
Even though column C1 does not exists, you will not get error.
Yes, I do agree that we as professionals must ensure that sub-query works.
😉
February 2, 2009 at 11:32 pm
Peter,
What you have explained over there is wrong.
If you run this one, it will execute succesfully.
IF NOT EXISTS(SELECT 1 FROM sys.server_principals b
WHERE b.sid=(SELECT b.sid FROM sys.databases a WHERE a.name=DB_NAME()))
BEGIN
PRINT N'Invalid Database Owner'
END
ELSE
BEGIN
PRINT N'Valid Database Owner'
END
But if you run
IF NOT EXISTS(SELECT 1 FROM sys.server_principals b
WHERE b.sid=(SELECT a.sid FROM sys.databases a WHERE a.name=DB_NAME()))
BEGIN
PRINT N'Invalid Database Owner'
END
ELSE
BEGIN
PRINT N'Valid Database Owner'
END
It would not run and hence as Hugo said your concept was wrong.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply