January 13, 2012 at 6:54 am
If I run the following query:
if cast(GETDATE() as time) > '12:00:00'
select GETDATE() into #Test
else
select GETDATE()-0.5 into #Test;
I get the following error message:
Msg 2714, Level 16, State 1, Line 4
There is already an object named '#Test' in the database.
Why is this so? The two cases of the if statement is mutually exclusive, so there is no way that both will be created. Does anyone know why this statement won't run? To me this seems like a bug.
January 13, 2012 at 6:58 am
Because the parser doesn't execute conditional logic (just like the optimiser doesn't). The parser just sees two statements that can create the same table and that's an error. Simplistic, yes, but it's the same kind of reason this fails
CREATE TABLE Test1 (
ID int
)
GO
ALTER TABLE Test1 ADD SomeColumn Varchar(10)
SELECT ID, SomeColumn FROM Test1
Msg 207, Level 16, State 1, Line 3
Invalid column name 'SomeColumn'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 6:59 am
Someone else will have to speak the the specifics of "WHY", but I believe it has something to do with the parser having to evaluate both conditions of the IF.
This is the workaround ...
CREATE TABLE #test (dt DATETIME)
IF CAST(GETDATE() AS TIME) > '12:00:00'
INSERT #test
SELECT GETDATE()
ELSE
INSERT #test
SELECT GETDATE() - 0.5
SELECT * FROM #test
DROP TABLE #test
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 13, 2012 at 7:11 am
okbangas (1/13/2012)
Gail: Don't you ever work? It seems that your response time in this forum is pretty impressive 😀
Supposed to be working now. Trying to figure out XPath query. Forums distracting....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 7:17 am
GilaMonster (1/13/2012)
okbangas (1/13/2012)
Gail: Don't you ever work? It seems that your response time in this forum is pretty impressive 😀Supposed to be working now. Trying to figure out XPath query. Forums distracting....
That's why I love BI work with reports regularly running around 1-2 minutes. Always enough time for a little peak here :hehe:.
January 13, 2012 at 7:23 am
okbangas (1/13/2012)
I'll have to explicitly create the table then.
Not necessarily.
select
GETDATE() - CASE WHEN cast(GETDATE() as time) > '12:00:00' THEN 0 ELSE 0.5 END [SomeDate]
into
#Test;
Edit: reversed THEN and ELSE part
January 13, 2012 at 7:30 am
Peter Brinkhaus (1/13/2012)
okbangas (1/13/2012)
I'll have to explicitly create the table then.Not necessarily.
My real life scenario is far more complex than this sample code, whose sole purpose were to demonstrate what caused the error message, so I have to create the table on before hand. 🙂
January 13, 2012 at 8:03 am
okbangas (1/13/2012)
so I have to create the table on before hand. 🙂
Or use dynamic SQL. That would work as well.
-- Gianluca Sartori
January 13, 2012 at 2:39 pm
Gianluca Sartori (1/13/2012)
okbangas (1/13/2012)
so I have to create the table on before hand. 🙂Or use dynamic SQL. That would work as well.
Thought of that one too, but don't like the idea of dynamic SQL being generated within dynamic SQL, that is kinda ugly 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply