Correct syntax for using nested cursors 1

[Sveno]

Hi,

I am after the correct syntax for using an outer cursor value in an inner cursor select statement.

My outside cursor looks something like:

declare outerloop cursor for
select fieldname from table1
declare @FNAME as varchar(50)
open outloop
fetch next from outerloop into @FNAME

My problem is I want to open the second cursor and use the @FNAME from the first cursor as the COLUMN value I now need. ie

select driverNM, @FNAME from table2

This is where I have come unstuck. I initally did this in ACCESS using recordsets (with the help of a good programmer) and we finally gave up as the only lead was EVAL, which just didn't work.

I have tried square brackets, single and double quotes and combinations of each, without any luck so far.

Cheers

Mark

 

[swampBoogie]

Use a join instead of nested cursors

select t1.col1,t2.col2 from t1 join t2 
  on t1.filename = t2.filename

There are very few cases when you need a cursor at all.

 

[hmckillop]

I dont understand what the problem is.
If you are trying to user @FNAME as the select for the inner cursor, why, the value in @FNAME from the first cursor will be available throughout the second cursor as I assum you are using this within one SP.
As it currently stands, In the inner cursor you will be then assigning another variable the value of @fNAME (fixed for the duration of inner cursor)

DECLARE outerloop CURSOR for
SELECT fieldname FROM table1
DECLARE @FNAME as varchar(50)
OPEN outloop
FETCH NEXT FROM outerloop INTO @FNAME

WHILE (@@FETCH_STATUS <> -1)
BEGIN

DECLARE innerloop CURSOR for
SELECT fieldname, @FNAME FROM table2
-- but note the value of @FNAME wont change inside inner cursor
DECLARE @FNAME as varchar(50)
OPEN innerloop
FETCH NEXT FROM outerloop INTO @InnerOtherfield, @InnerFNAME
WHILE
--do stuf in here
FETCH NEXT FROM outerloop INTO @InnerOtherfield, @InnerFNAME

END
FETCH NEXT FROM outerloop INTO @FNAME

END -- outer cursor



The other question i would have is, have you examined the possibility of removing some or all of the cursors?

Sorry this might not be of any help, explain further what you are trying to accomplish. Either that or I have misread what you are trying to do.





"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[Sveno]

Thank you for responding, sorry I have not stated this problem too clearly. I need to use cursors as this procedure updates values in a model. The first cursor lists the team names, the first inner cursor the driver names and the second inner cursor updates the values (per team per driver) using the weightings from another table.

I have used this approach successfully before, but I am just stuck on the syntax this time.

From the example, if the value of @FNAME was "Number of Staff", in the inner cursor the select should be:

select field1, [Number of Staff] from table2. Using select field1,@FNAME from table2 just gives me an error.

Cheers

Mark

 

[bertrandkis]

Hi Sveno,
I think I understand your problem and I can help,here we go.
Your original script looks like this:
DECLARE outerloop CURSOR for
SELECT fieldname FROM table1
DECLARE @FNAME as varchar(50)
OPEN outloop
FETCH NEXT FROM outerloop INTO @FNAME

WHILE (@@FETCH_STATUS <> -1)
BEGIN

DECLARE innerloop CURSOR for
SELECT fieldname, @FNAME FROM table2
-- Notice that in this second select statement, you have
-- a variable name that must be resolved before the select
-- is executed.In such case you should use dynamic execution to be able to execute a select statement like this.Using dynamic execution you will do this:
Execute('select fieldname,'+ @FNAME +'FROM table2')
In this case dynamic execution will execute your statement but the cursor will not be created. In fact if you try to do this:
DECLARE innerloop CURSOR for
Execute('select fieldname,'+ @FNAME +'FROM table2')
--You will get an incorrect syntax error.
-- So because of this you have reached a dead end.

--Now my proposed solution is this, as part of your script
create a temporary table as below.

Create Table #temptable (Pk int identity,Counter int,col1 varchar(50),col2 varchar(50))
--then create your outerloop cursor
DECLARE outerloop CURSOR for
SELECT fieldname FROM table1
DECLARE @FNAME as varchar(50)
Declare @ctr int --declare a variable to use as a counter
Declare @sql varchar(300) --to hold your dymanic select
set @ctr=1 -- initialize the variable
OPEN outloop
FETCH NEXT FROM outerloop INTO @FNAME

WHILE (@@FETCH_STATUS <> -1)
BEGIN
set @sql='select'+cast(@ctr as varchar(5))+' fieldname,'+ @FNAME +'FROM table2'

Insert into #temptable(Counter,col1,col2)
Execute(@sql)
--Notice that we use dynamic execution to populate the table.This works.
set @ctr=@ctr+1
FETCH NEXT FROM outerloop INTO @FNAME
END
close outerloop --close and destroy outerloop cursor
Deallocate OuterLoop

--Now all the data that would make up the inner cusor are stored in your temp table.Values returned from each field name @FNAME have a corresponding value of @ctr i.e if you run a select statement against #temptable, all rows having counter=1 are rows returned when @FNAME had it's first value,all rows having counter=2 are rows returned when @FNAME had it's second value and so on......
--You can now work on the data in the temptable and do what ever you inteded to do in the inner cursor without
--even creating another cursor because your #temptable has an idenity column
--So this is what you will do
set @ctr=1
While @ctr<=(select Max(pk) from #temp)
Begin
--do here all the stuff you wanted to do in your inner cursor
set @ctr=@ctr+1 --increment your loop counter
end
--drop the temp table
Drop table #temptable.

Et Voila.
This solution is tested and certified correct by Bertrandkis.

 

[Sveno]

Thank you for this excellent idea!! I have it working in SQL-Server and it will work in Access as well. I never thought of creating a temporary table, I will remember this idea from now on.

Many Thanks

Cheers

Mark