November 6, 2009 at 3:21 am
I am trying to populate a temp table from the sysjobhistory of various servers based on a parameter in a procedure. I thought it would be fairly straightforward:
DECLARE @server sysname
SET @server = 'bovril'
IF (@server = 'bovril') BEGIN
SELECT *
INTO #hist
FROM msdb.dbo.sysjobhistory
END
ELSE IF (@server = 'lowenbrau') BEGIN
SELECT *
INTO #hist
FROM lowenbrau.msdb.dbo.sysjobhistory
END
ELSE IF (@server = 'sfqcnt2') BEGIN
SELECT *
INTO #hist
FROM sfqcnt2.msdb.dbo.sysjobhistory
END
Unfortunately I get:
Msg 2714, Level 16, State 1, Line 12
There is already an object named '#hist' in the database.
Msg 2714, Level 16, State 1, Line 17
There is already an object named '#hist' in the database.
It seems to be trying to run, the code even when the condition is not met.
I have an easy enough solution:
SELECT *
INTO #hist
FROM msdb.dbo.sysjobhistory
WHERE 1=2
SET INDENTITY_INSERT #hist ON
IF (@server = 'bovril') BEGIN
INSERT INTO #hist(.....)
SELECT *
FROM msdb.dbo.sysjobhistory
END
ELSE IF.....
I remain exceedingly puzzled as to the behaviour however, and was hoping someone could explain to further my understanding.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
November 6, 2009 at 3:33 am
Hi Rob
SQL Server analyzes your statement before it becomes executed. Your first IF-block uses INSERT INTO ... Both other blocks use SELECT INTO. Since your temp table was already created above the IF part this cannot be executed.
Use INSERT INTO within your other IF blocks and everything should work find.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
November 6, 2009 at 3:50 am
Hey Rob,
Check this out 🙂
--
-- This doesn't work
--
DECLARE @Choice INTEGER;
SET @Choice = 3;
IF @Choice = 1
BEGIN
SELECT TOP (1) a = 1 INTO #A FROM sys.databases
END
ELSE IF @Choice = 2
BEGIN
SELECT TOP (1) a = 2 INTO #A FROM sys.databases
END
ELSE IF @Choice = 3
BEGIN
SELECT TOP (1) a = 3 INTO #A FROM sys.databases
END;
GO
--
-- This does
--
DECLARE @Choice INTEGER;
SET @Choice = 3;
SELECT TOP (1)
a = 1
INTO #A
FROM sys.databases
WHERE @Choice = 1
UNION ALL
SELECT TOP (1)
a = 2
FROM sys.databases
WHERE @Choice = 2
UNION ALL
SELECT TOP (1)
a = 3
FROM sys.databases
WHERE @Choice = 3
GO
SELECT * FROM #a;
GO
DROP TABLE #a;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 4:04 am
...compare the plan for the example that does work (with the UNIONs) against the plan for this:
--
-- This also works
--
DECLARE @Choice INTEGER;
SET @Choice = 3;
IF @Choice = 1
BEGIN
SELECT a = 1 INTO #A FROM sys.databases
END
ELSE IF @Choice = 2
BEGIN
SELECT a = 2 INTO #B FROM sys.databases
END
ELSE IF @Choice = 3
BEGIN
SELECT a = 3 INTO #C FROM sys.databases
END;
You should see that SQL Server compiles the whole batch (for re-use) so it can't produce a single plan in the case where all the tables are named #A, even though logically only one path can be taken. In the case with the UNIONs, the various inputs are concatenated before creating table #A once from the result, so it is easy for the server to generate a nice re-usable plan.
Let me know if that doesn't make sense to you 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 5:38 am
Yeah, makes sense.
I have the union one in there anyway because I need to reference by same table name later on.
It's basically for a wee html dashboard of jobs on the various servers so it's not going to get hit that hard anyway but it's always handy to look at performance issues on these things which I shall do in more detail when I get the chance.
Cheers.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
November 6, 2009 at 6:06 am
Cool. The other option would be dynamic SQL, but personally I like the UNION.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 6:45 am
Yes. Though I did not specifically mention so I was aiming to intimate that I was avoiding the dynamic where possible.
Now for the fun stuff, the DHTML tooltip of the actual error on hover.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
November 6, 2009 at 6:18 pm
Rob Fisk (11/6/2009)
Yes. Though I did not specifically mention so I was aiming to intimate that I was avoiding the dynamic where possible.
Yeah - I'm naturally anti-red-text too.
Rob Fisk (11/6/2009)
Now for the fun stuff, the DHTML tooltip of the actual error on hover.
If you think DHTML is fun, you need to get out more :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2009 at 3:29 am
Oh, I do the strangest things for fun. The DHTML stuff was mainly code theft so the fun bit was actually seeing the end result in action and now we have a nice jobs dashboard that emails us of any failures, even those on the old 2k boxes we don't have an old office license to make email work properly with.
One of my "for fun" projects involved some hideous red text, a procedure to generate insert statements for any table. As you can probably imagine, the nested escaping of single quotes got somewhat crazy.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply