Using Temporary Tables In A Stored Procedure 2

[Skittle]

I have a stored procedure that ends with a SELECT statement.
The stored procedure returns a table that holds values populated by the stored procedure itself based on run time parameters.

I use the stored procedure as input into Crystal Reports.

A manager has now seen my procedure ( always fatal ) and he wants access for himself and a few others. Since this procedure manipulates standard database tables as work tables, I need to modify the stored procedure so that the work tables are unique to each Crystal user session. That way more than one user can use the procedure at the same time without screwing each other up.

Am I correct that the best way to solve this is to change my work tables to temporary tables ( # prefix ) within the procedure and create them as part of the procedure?

Are there other options?






Dazed and confused

 

[SQLDenis]

yes, that is correct temporary tables ( 1 # not 2) will solve your problem



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Personally, I would use table variables instead of temp tables. Often times, the performance is better. It's a little 'hit or miss' though because some times temp tables are faster than table variables.

Take a look at this faqs faq183-1100 to see how table variables work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>Personally, I would use table variables instead of temp tables

I agree with you, however it is faster to prefix the tables with # than to write declare @someName table in the code

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

Good point. I see where you're coming from. Yeah, it would be faster to modify the existing code to use temp tables. Table variables would require more modification, but would probably result in faster execution times.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Skittle]

Cool!

I shall investigate both options.
Many, many thanks.

Dazed and confused