asked on

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.

rmacfadyen

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

sdc248

ASKER

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?

ASKER CERTIFIED SOLUTION

rmacfadyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdc248

ASKER

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?

rmacfadyen

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

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdc248

ASKER

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.