Error using cursor within a cursor in t-sql

[dataforums]

Hi guys,

I think this a simple error but i haven't been able to figure out what's wrong with the code below:

Declare temp_cur CURSOR FOR
select table_name from INFORMATION_SCHEMA.TABLES where table_name like 'SAMP_TMP_%'

OPEN temp_cur
FETCH NEXT FROM temp_cur into @temp_tab

WHILE @@FETCH_STATUS <> -1

BEGIN

declare t_cur cursor for select * from @temp_tab

OPEN t_cur
FETCH NEXT FROM t_cur INTO @id,..................... @type

WHILE @@FETCH_STATUS <> -1
BEGIN

-- some operation performed

FETCH NEXT FROM t_cur INTO @id,..................... @type

END

CLOSE t_cur
deallocate t_cur

FETCH NEXT FROM temp_cur INTO @temp_tab
END

CLOSE temp_cur
deallocate temp_cur
**********************************************************

Any idea what the problem might be?

Thanks in Advance!

 

[JamesLean]

You can't use a variable in place of an object name (in your second DECLARE CURSOR statement).

Looking at this script I would be surprised if you really need to use a cursor for this. Maybe if you posted what you are trying to achieve someone could suggest a better set-based method.

--James

 

[hmckillop]

What error do you get?
have you declared your local variables (e.g. temp_cur).

Whats the full code?

It looks ok apart from no declaration for @temp_tab

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Ooops

Well spotted JamesLean


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dataforums]

I get the follwoing error:

Server: Msg 137, Level 15, State 2, Line 31
Must declare the variable '@temp_tab'.

Can u suggest me as to how to loop through the table that comes from the variable temp_tab?

 

[JamesLean]

Why do you want to loop through all these tables? As I said above, if you explain what you're trying to do there may be alternatives to using a cursor.

--James

 

[dataforums]

I have to check some conditions and load data from all these temperory tables into their respective permanent tables. so i have to loop thorugh each table and update their permanent table. I have done the same thing in oracle using REF Cursor, but i don't know if t-sql has ths concept.