Open cursor from dynamic temporary table

[hungerf5]

Having difficulty opening a cursor from a dynamically created temp table.

I'm creating a temporary table based upon a processes @@SPID value. I then populate the table using sp_executesql.

How can I open a cursor based on the dynamically created temporary table?

I've opened it successfully by using code similar to below:
DECLARE @srtSQL VARCHAR(1024)

SET @strSQL = 'DECLARE @CURSOR CURSOR GLOBAL FOR @CURSOR SELECT * FROM ' + @TEMP_TABLE_NAME

EXECUTE sp_executesql @strSQL

This seems to work but the only way to continue using the cursor is to call it by building another string and executing it. If you try to access the @CURSOR the Stored Proceedure will not complile. Must declare the variable @CURSOR

Im looking for a way to do something like below.

DECLARE @CURSOR CURSOR
SET @CURSOR = CURSOR FOR SELET * FROM[/color] "DYNAMIC TEMP TABLE"

There has to be a way to do this.... Is there some sort of way to make an alias for the table name?

Maybe I'm taking the wrong approach to this whole thing. What I'm trying to avoid is the Stored Proceedure running twice at the same time. If I don't give the tables unique names I'll run into problems right? I say yes.

Any suggestions? I'm fairly new to SP writing so hopefully I'm doing a decent job describing what my issues are.

--Rick

 

[tlbroadbent]

Why bother with a dynamically created temporary table? Local temporary tables are limited in scope to the current connection or SPID. Multiple users can concurrently execute a SP that creates a temporary table named #temp and each would be working in his own table. SQL handles naming internally so there is no chance of accessing the same table. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLSister]

Cursors are to be avoided at all costs as they are SLOW. There is almost alwys another way to do things. For better advice about what you should do instead, please post a description of the actions you are trying to get to happen.

 

[hungerf5]

I'm trying to create a set of stored procedure that generate a Bill of Material. One of the stored procedures is recursive.

Below is an article I'm using as a guide to write the SP's.

http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/usingt-sql.asp

I'll have to test if the table I'm trying to create will be accessible if I make it a local table.

--Rick

 

[hungerf5]

Here's a clip from the article that leads me to beleive I need the tables global....

"Using a global temporary table and SPID lets the table persist across stored procedures and support simultaneous multiple instances in each stored procedure. (For information about the differences between local and global temporary tables, see the sidebar "Temporary Tables: Local vs. Global.&quot To get a list of all current connections and their associated SPIDs, you can run the sp_who system stored procedure. (For more information about SPIDs, see SQL Server Books Online—BOL.)"