Creating tables in SP

Question

Creating tables in SP

Maqsood Ahmed

SSC Enthusiast

Points: 123
More actions
May 31, 2006 at 7:05 am

#113825

Hello,
I am trying to create a temporary table dynamically in a stored procedure to return some data. I can not create it statically as I don't know before hand that how much data will be there.
I tried sp_executesql and Execute(<>) both, but the table is not created, and no error is displayed.
Any thoughts?
Maqsood Ahmed
Kolachi Advanced Technologies
http://www.kolachi.net

Viewing 5 posts - 1 through 4 (of 4 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

You can create the temp table statically with just an IDENITITY column, say, and then add columns using an ALTER TABLE statement in dynamic SQL. the ability to do this is one of the few advantages of temp tables over table variables I'd say.

You might not need to use a tep table of course. Can we see a code example for the kind of thing you are trying to do?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Martin N Ten Centuries Points: 1216 More actions · Answer 2

I've got a feeling that when you use the EXECUTE (and probably sp_executesql) command that SQL ends the batch and hence destroys temp table. So you might be creating the table but it's instantly being dropped by SQL, which would expain why you see no table and get no error.

I'd use a standard table to do the load... Create the standard table using sp_execute_sql, use it however you need to, and at the end of your procedure drop the table...

Should have the same effect as as using a #temp table.

Kindest Regards,

Martin

stax68 SSChampion Points: 11711 More actions · Answer 3

Yes that is what happens to temp tables created in dynamic SQL. That's why I suggested creating a stub of a temp table in the calling process, which the dynamic SQL can then add columns to.

But remember if it's created in a stored proc it will be dropped when that stored proc ends, too.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Maqsood Ahmed SSC Enthusiast Points: 123 More actions · Answer 4

Hello,

Thanks stax. It had solved the problem.

Maqsood Ahmed
Kolachi Advanced Technologies
http://www.kolachi.net