Table Variables and Dynamic sql

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

Transact-SQL (2000)

Table Variables and Dynamic sql

Author

Topic

mikhail
Starting Member

12 Posts

Posted - 2004-06-17 : 15:44:06

I have one pretty long stored procedure which is using temp tables. I am trying top switch from temp tables to temporary variables.

The problem I am having that if I declare the table like this

create table @results
(
productid_pk int not null, --0
part varchar(30), --1
mfgcode_fk varchar(30), --2
mfgpart varchar(30), --3
product varchar(40), --4
brandname varchar(30) --5
)

then declare

declare @sql varchar(1000)
select @sql='insert into @results ...'

I am getting an error message, @results is not declared

Thanks

Mikhail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-17 : 16:12:52

You're not getting the error in the code you are displaying; rahter when you try to execute the dynamic SQL. that is because @results is not declared within the scope if the SQL you are generating.

Why do you need dynamic SQL ? Why are you moving everything from temp tables to table variables?

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 16:14:23

variables defined outside of a dynamic sql statement are not accessible in the dynamic sql statement

you would have to include the 'create table...' inside the sql string

Edit: D'oh too slow again

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-17 : 16:25:10

This freaked me out...

DECLARE @sql varchar(8000)

SELECT @sql = 'DECLARE @x int SELECT @x = 1 SELECT @x'

EXEC(@sql)

-- This will fail
--SELECT @x

So, while it's not in the context of this session, it's done at the end the execute...does it launch another spid like xp_cmdshell?

Oh, and yeah, to do what you want, you'd need a permanent table, which using as a work table in a sproc, is not a good idea, unless it's in a batch process that will only be executed once.

And I don't think a #temp table would work either...different session...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 16:28:04

You could use global temporary tables.

Tara

mikhail
Starting Member

12 Posts

Posted - 2004-06-17 : 17:04:20

Thanks for your comments , guys... this multiple select statements would work fine, if you have small sql statement but the actual statements are very long and dynamic also.

Somebody mentioned that global temporary table can be used, how would it help ?

Mikhail

quote:
Originally posted by X002548

This freaked me out...

DECLARE @sql varchar(8000)

SELECT @sql = 'DECLARE @x int SELECT @x = 1 SELECT @x'

EXEC(@sql)

-- This will fail
--SELECT @x

So, while it's not in the context of this session, it's done at the end the execute...does it launch another spid like xp_cmdshell?

Oh, and yeah, to do what you want, you'd need a permanent table, which using as a work table in a sproc, is not a good idea, unless it's in a batch process that will only be executed once.

And I don't think a #temp table would work either...different session...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 17:07:32

With a global temporary table, you'd be able to see it inside the dynamic sql session.

Tara

mikhail
Starting Member

12 Posts

Posted - 2004-06-17 : 17:36:44

What would happen in the multi-user situation if I start using global temporary table ?

quote:
Originally posted by tduggan

With a global temporary table, you'd be able to see it inside the dynamic sql session.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 17:38:52

That would definitely cause a problem. Could you show us what you are trying to do, why you need dynamic sql?

Tara

mikhail
Starting Member

12 Posts

Posted - 2004-06-17 : 17:41:54

The stored Procedure is 450 lines long...
I wish I can find somebody who can help me optimize it, ready to pay for it..don't want to publish to all users though...

quote:
Originally posted by tduggan

That would definitely cause a problem. Could you show us what you are trying to do, why you need dynamic sql?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 18:15:40

Create a regular temp table instead of the @table (which you use DECLARE @table TABLE for btw). In your dynamic SQL strip out the INSERT @table and just build the select. Insert the results into the temp table by using:

INSERT #table(col1)
EXEC sp_executesql @sql

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mikhail
Starting Member

12 Posts

Posted - 2004-06-17 : 20:17:35

I was trying to enhance performance by moving stuff from temp tables to table variables !

The way stored procedure works.
1. Has more then 20 parameters
2. 2 temporary tables are created
3. It runs dynamic sql statement and inserts results of it into the first temporary table
4. After that I dynamically join this temporary table with some other tables in multiple databases and create the final results
5. Then I only select records for one page from the last temporary table (typical sql paging)

I was trying to get rid of temp tables...

Mikhail

quote:
Originally posted by derrickleggett

Create a regular temp table instead of the @table (which you use DECLARE @table TABLE for btw). In your dynamic SQL strip out the INSERT @table and just build the select. Insert the results into the temp table by using:

INSERT #table(col1)
EXEC sp_executesql @sql

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 21:06:21

You can't get rid of temp tables because you can't insert the results of dynamic SQL into table variables. It will be supported in SQL 2005 if anyone cares, but for now we're stuck with temp tables. Many times you can figure out a way to get completely around this though. Search the forum for paging. You'll find all kinds of examples to do what you're trying to do.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-18 : 00:38:46

Unless your table variable only holds a few hundred rows you are probably better off using a temp table anyway. Statistics are not kept for table variables and using them can prevent a query from generating parallel query plans. Table variables are saved to disk anyway once they get to be above a certain size.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 00:51:25

Microsoft recommends a cutoff of 10,000 rows. My research and experience seems to confirm that's about where the performance starts shooting downhill and temp tables are better.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-18 : 01:10:15

My info came from a technet chat http://www.microsoft.com/technet/community/chats/trans/sql/sql1022.mspx where the guy suggested it was in the neighborhood of hundreds, and just recently I was monitoring IO stats and saw 'worktable' IO that I think was the temp table variable. (edit: BTW The table vriable had 1000 rows. I was using it as a sequence. A temp table performed better, but I had pre-created it.) From the article:

Q:if a table variable can do the job, are there any cases where a #temp table is a better choice?
A: In fact yes. Temp tables (# or ##) can in fact be better in some cases. I didn't know this until recently but a member of the SQL development team shared some interesting info with us recently. Table variables do not have statistics maintained for them. SQL makes guesses about how big they will be. I forget the exact number but we're talking hundreds of rows. You might get a very bad plan if you join a table variable that is VERY big since stats will be way off. Also, (I haven't tested this much...) MS has told us that table variables can keep a plan from going parallel if the parallel plan would otherwise have been an efficient strategy. Finally... you'll find that table variables do in fact take up space and might spool to disk in tempdb just like a 'normal' temporary table might.

Kristen
Test

22859 Posts

Posted - 2004-06-18 : 01:13:51

Just by the by, I've noticed absolutely dreadful performance on Table Vars (#TEMP's too I suppose) when we left off the PRIMARY KEY definition and then joined them to something else. They would have been small-ish tables, and I would ahve thought that being in memory etc. a table scan would have been fine, but seems not.

Just 'coz its a TEMP doesn't seem to mean that the DBA doesn't have to get the big bat out and wander down to the devlopers department ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 01:34:50

I always create a primary key on mine. Anyone else want to chime in on the cons/pros of primary keys with table variables? :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources