nested cursors , where oh where am I going wrong with this

Question

nested cursors , where oh where am I going wrong with this

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
January 21, 2008 at 8:58 am

#178377

soon I will have no hair left to tear out and for me just hitting middle age thats not good
the following code fails when it says must declare the @tbl variable within the nested cursor
help !! please
simon
--
DECLARE @TBL varchar(32),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@person_code varchar(15),
@ID varCHAR (10),
@max_no varchar(3),
@report_code varchar(3)
SET @ctrl = CHAR (13) + CHAR (10)
set @person_code = '1'
set @ID = '997'
create table #temp_number
(table_name varchar (50),
report_code varchar (10),
REPORT_NO varchar (10)
)
DECLARE TBLCUR CURSOR FOR
select distinct table_name
from information_schema.columns where column_name = 'REPORT_NO'
order by 1
OPEN TBLCUR
FETCH NEXT FROM TBLCUR INTO @TBL
WHILE @@FETCH_STATUS = 0
BEGIN
declare value cursor for
select report_code, max(REPORT_NO)
from @TBL
where person_code = @person_code
and ID = @ID
--
OPEN value
--
FETCH NEXT FROM value INTO @report_code,@values
--
WHILE @@FETCH_STATUS = 0
BEGIN
insert #temp_number (table_name)
values (@tbl)
update #temp_number
set REPORT_NO = @values,
report_code = @report_code
where table_name = @tbl
FETCH NEXT FROM value INTO @report_code,@values
end
CLOSE value
DEALLOCATE value
FETCH NEXT FROM TBLCUR INTO @TBL
end
CLOSE TBLCUR
DEALLOCATE TBLCUR
go
DROP TABLE #temp_number

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

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 1

I'm not 100% sure, but it's possible that you can't open two cursors on a table variable.There might be some scope issue here.

Any reason not to move this to a temp table and use that?

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

Steve Jones - Editor (1/21/2008)
I'm not 100% sure, but it's possible that you can't open two cursors on a table variable.There might be some scope issue here.
Any reason not to move this to a temp table and use that?

changed #temp_number to temp_number , still get

Server: Msg 137, Level 15, State 2, Line 42

Must declare the variable '@TBL'.

Server: Msg 137, Level 15, State 1, Line 47

Must declare the variable '@values'.

weeps 🙁

si

GSquared SSC Guru Points: 260824 More actions · Answer 3

You're declaring @TBL as a varchar, then trying to select from it. You can't select from a varchar variable.

Either @TBL needs to be declared as variable type "table", and your first cursor needs to be re-written to insert into that table, or the second cursor needs to be rewritten to select differently.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

From what I can see, you'll need to use some dynamic SQL to select from @TBL.

Try replacing this

declare value cursor for

select report_code, max(REPORT_NO)

from @TBL

where person_code = @person_code

and ID = @ID

with this

declare @SQL nvarchar(4000)

set @SQL=N'declare value cursor for

select report_code, max(REPORT_NO)

from '+@TBL+'

where person_code = @person_code

and ID = @ID '

execute sp_executesql

@SQL,

N'@person_code varchar(15),@ID varchar (10)',

@person_code = @person_code,

@ID = @ID

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537