nested cursors , where oh where am I going wrong with this
-
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
-
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?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
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
-
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
-
From what I can see, you'll need to use some dynamic SQL to select from @TBL.
Try replacing this
declare value cursor forselect 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply