Error using cursor within a cursor in t-sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error using cursor within a cursor in t-sql

Author

Topic

dataforums
Starting Member

14 Posts

Posted - 2005-05-05 : 10:43:35

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!

dataforums
Starting Member

14 Posts

Posted - 2005-05-05 : 11:08:34

I got to know it is not valid to declare a variable in place of an object name.

declare t_cur cursor for select * from @temp_tab

But can anyone suggest me as to how i can achieve looping through this table in the variable temp_tab?

Thanks!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-05 : 12:57:48

Please see the FAQ (http://www.sqlteam.com/faq.asp) for articles on Dynamic SQL.

I'd also look at Books Online. I think they have an example where you use dynamic SQL to populate a temporary table and then use a cursor over that. It will be slow though.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources