• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

using stored procedure or function to create/insert temporary table

Hi guys:

What is the best approach to code sub-procedure in T-SQL? I am thinking of using sp or function in one of my sp to behave like a sub-function where it would insert data to a temporary table for the main sp to use. Something like:

create proc spMain(....)
begin
create table #temptable(....)
spSub()  -- sp that populate the #temptable
select * from #temptable
 ......
drop #temptable
end

I want to do this because depending on the input parameters, I would populate the temporary table with different data. Kindly let me know if this approach has any potential problems.  Thanks.
0
sdc248
Asked:
sdc248
  • 3
  • 3
2 Solutions
 
rmacfadyenCommented:
There's a couple of ways to do this. You can define your subroutine as a separate stored procedure and just invoke it from the main stored procedure using the EXEC command. Another way is to create a user-defined function, though user-defined are more appropriate for routines that return a value (or list of values).

Regards,

Rob
0
 
sdc248Author Commented:
Would it be a problem if I run multiple copy of this main procedure at the same time, which means, multiple versions of sub sp would be trying to populate supposedly different copies of #temptable?

If I put "create table #temptable(...)" in the sub sp, could the "drop #temptable" code in the spMain() know which copy of #temptable to drop?
0
 
rmacfadyenCommented:
Ah... I should have read your question a bit more carefully. The short answer is "I don't know", my guess is "maybe". I know EXEC to invoke dynamic SQL creates a separate connection... so it would have its own temp tables. Would EXEC of a stored proc do the same... probably not... but you'd need to test it carefully. The test is fairly simple... have a sub-stored procedure create the temp table and have the main stored procedure populate it. If this succeeds then you _know_ both are sharing the same connection.

Regards,

Rob
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdc248Author Commented:
Tested as suggested and got "Invalid object name '#temptable'" from the main proc.

So the better way is to use a function to create/populate the table and return it, I guess? Would it be a performance hit if the table is huge, e.g. 1 million rows?
0
 
rmacfadyenCommented:
A million rows! That seems like a lot for temptable. Heck for a single stored proc even... that's a lot of record creates. I would serious reconsider whether a temptable is right answer... what happens if the server hiccups in the middle of the process, or more likely on record 999,999? You'd have to start from scratch again. Plus there's the whole disk space issue... using a temptable means the tempdb ought to be sized appropriately (.mdf and .ldf)... growing dynamically can be really slow.

If I where you I'd be more concerned about the performance than about the structure of the stored proc. Are certain of the volume? Also I have no idea how SQL handles the drop side of the temptable... no idea how long a drop of a table that size would take.

You _might_ be better off with an actual table, maybe in a separate database. If the million records really are temporary in nature having them in a separate database might make the drop side of things go fast (just delete the database).

I'd also be very careful about transaction scoping. Having a single transaction with 1 million inserts is not a good idea. Nor is a million transactions. Maybe batches of 100 or 1000 per transaction.

Regards,

Rob
0
 
Scott PletcherSenior DBACommented:
>> If I put "create table #temptable(...)" in the sub sp, could the "drop #temptable" code in the spMain() know which copy of #temptable to drop? <<

No, but if you create the temp table in the main sp, a sub-proc (an EXEC of another sp) would be able to use it.  In fact, I think dynamic SQL can use it, so long as the temp table exists before the dynamic SQL is run.
0
 
sdc248Author Commented:
Thank you guys.

The maximum number of rows would be 1.3 millions. I think choping up into batches to run is a good idea.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now