table variable inside a cursor

Question

table variable inside a cursor

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Craig Farrell (11/1/2010)
Of course to make my life interesting, this gets grumpy too and I can't remember how to get it to behave offhand. Maybe someone else remembers how to get this to behave without GO statements to split the script.
CREATE TABLE #tmp (tID INT);
DROP TABLE #tmp;
CREATE TABLE #tmp (tID INT, tText VARCHAR(200));

I don't know about 2k8 but the code above causes the following error in 2K5 sp3...

[font="Courier New"]Msg 2714, Level 16, State 1, Line 3

There is already an object named '#tmp' in the database.[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Craig,

Thanks for the clarification.

Thanks & Regards,
MC

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Jeff Moden (11/1/2010)
Craig Farrell (11/1/2010)
Of course to make my life interesting, this gets grumpy too and I can't remember how to get it to behave offhand. Maybe someone else remembers how to get this to behave without GO statements to split the script.
CREATE TABLE #tmp (tID INT);
DROP TABLE #tmp;
CREATE TABLE #tmp (tID INT, tText VARCHAR(200));
I don't know about 2k8 but the code above causes the following error in 2K5 sp3...
[font="Courier New"]Msg 2714, Level 16, State 1, Line 3
There is already an object named '#tmp' in the database.[/font]

Still fails in 2k8 SP1. I don't have R2 to test there.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Craig Farrell (11/1/2010)
Of course to make my life interesting, this gets grumpy too and I can't remember how to get it to behave offhand. Maybe someone else remembers how to get this to behave without GO statements to split the script.
CREATE TABLE #tmp (tID INT);
DROP TABLE #tmp;
CREATE TABLE #tmp (tID INT, tText VARCHAR(200));

The only option I see is to drop / alter the columns with the new definition or use a 2nd temp table, assuming the insert hit is low.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Yeah, even this doesn't work:

CREATE TABLE #tmp (tID INT);

IF Object_id('#tmp') IS NOT NULL

BEGIN

DROP TABLE #tmp

END;

IF Object_id('#tmp') IS NULL

BEGIN

CREATE TABLE #tmp(tID INT, tTxt VARCHAR(50))

END;

So, I'm going to go with I had a bit of crack smoking going on for the double declare. Apparently the pre-compiler has an issue with a dual declare on the same table at any point. Probably a good thing. Not sure why I remembered doing that unless it was cascading procs.

So, sorry for that. The rest was accurate.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Craig Farrell (11/2/2010)
Yeah, even this doesn't work:
CREATE TABLE #tmp (tID INT);
IF Object_id('#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp
END;
IF Object_id('#tmp') IS NULL
BEGIN
CREATE TABLE #tmp(tID INT, tTxt VARCHAR(50))
END;
So, I'm going to go with I had a bit of crack smoking going on for the double declare. Apparently the pre-compiler has an issue with a dual declare on the same table at any point. Probably a good thing. Not sure why I remembered doing that unless it was cascading procs.
So, sorry for that. The rest was accurate.

Well you can't say I didn't try!

CREATE TABLE #tmp (tID INT);

IF Object_id('tempdb..#tmp') IS NOT NULL

BEGIN

DROP TABLE #tmp

END;

IF Object_id('tempdb..#tmp_tmp') IS NOT NULL

BEGIN

DROP TABLE #tmp_tmp

END;

CREATE TABLE #tmp_tmp(tID INT, tTxt VARCHAR(50))

exec sp_rename 'tempdb..#tmp', 'tempdb..#tmp', 'OBJECT'

SELECT * FROM #tmp

And no that doesn't work either

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Even if it makes no difference here, it should read : IF Object_id('tempdb..#tmp') IS NOT NULL

With tempdb.. missing you'll never have a hit on that id.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Ninja's_RGR'us (11/2/2010)
Even if it makes no difference here, it should read : IF Object_id('tempdb..#tmp') IS NOT NULL
With tempdb.. missing you'll never have a hit on that id.

Hmmmm....

CREATE TABLE #tmp (tID INT);

IF Object_id('#tmp') IS NOT NULL

BEGIN

PRINT 'No tempdb. needed'

END;

IF Object_id('tempdb..#tmp') IS NOT NULL

BEGIN

PRINT 'tempdb. WAS needed'

END;

Hm, well shucks. Here's my failure point on realizing that... 😀

USE tempdb

GO

CREATE TABLE #tmp (tID INT);

IF Object_id('#tmp') IS NOT NULL

BEGIN

PRINT 'No tempdb. needed'

END;

IF Object_id('tempdb..#tmp') IS NOT NULL

BEGIN

PRINT 'tempdb. WAS needed'

END;

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Craig Farrell (11/2/2010)
Ninja's_RGR'us (11/2/2010)
Even if it makes no difference here, it should read : IF Object_id('tempdb..#tmp') IS NOT NULL
With tempdb.. missing you'll never have a hit on that id.
Hmmmm....
CREATE TABLE #tmp (tID INT);
IF Object_id('#tmp') IS NOT NULL
BEGIN
PRINT 'No tempdb. needed'
END;
IF Object_id('tempdb..#tmp') IS NOT NULL
BEGIN
PRINT 'tempdb. WAS needed'
END;
Hm, well shucks. Here's my failure point on realizing that... 😀
USE tempdb
GO
CREATE TABLE #tmp (tID INT);
IF Object_id('#tmp') IS NOT NULL
BEGIN
PRINT 'No tempdb. needed'
END;
IF Object_id('tempdb..#tmp') IS NOT NULL
BEGIN
PRINT 'tempdb. WAS needed'
END;

:w00t::w00t::w00t:

Ok so temdb is not acutally required to be in the object_name :hehe:.

I'd love to see a proc like this :

use ssc

create #x (a int)

use tempdb

if object_id('#x') is not null

begin

use ssc

drop table #x

end

WayneS SSC Guru Points: 95460 More actions · Answer 10

Ninja's_RGR'us (11/2/2010)
Craig Farrell (11/2/2010)
Ninja's_RGR'us (11/2/2010)
Even if it makes no difference here, it should read : IF Object_id('tempdb..#tmp') IS NOT NULL
With tempdb.. missing you'll never have a hit on that id.
Hmmmm....
CREATE TABLE #tmp (tID INT);
IF Object_id('#tmp') IS NOT NULL
BEGIN
PRINT 'No tempdb. needed'
END;
IF Object_id('tempdb..#tmp') IS NOT NULL
BEGIN
PRINT 'tempdb. WAS needed'
END;
Hm, well shucks. Here's my failure point on realizing that... 😀
USE tempdb
GO
CREATE TABLE #tmp (tID INT);
IF Object_id('#tmp') IS NOT NULL
BEGIN
PRINT 'No tempdb. needed'
END;
IF Object_id('tempdb..#tmp') IS NOT NULL
BEGIN
PRINT 'tempdb. WAS needed'
END;
:w00t::w00t::w00t:
Ok so temdb is not acutally required to be in the object_name :hehe:.
I'd love to see a proc like this :
use ssc
create #x (a int)
use tempdb
if object_id('#x') is not null
begin
use ssc
drop table #x
end

Are you saying that OBJECT_ID('#tmp') returned a not null value for you? This little test returns a null for me.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Yes, but only when running the code in tempdb, that's why I was having a little fun with it in the code snippe with a bunch of usings.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 12

But again, if there is a CREATE table it will give the same error as before right..?

USE SSC

CREATE #X (A INT)

USE TEMPDB

IF OBJECT_ID('#X') IS NOT NULL

BEGIN

USE SSC

DROP TABLE #X

CREATE #X (A INT) -- I mean this line

END

Thanks & Regards,
MC

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Of course, I was just having fun with the tempdb thingie.