Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple variables in fetch

I can not assign more than one variable using this cursor:
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemnameWhat does "can not" mean? Do you get an error message? If so, what is it?
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||The @.itemname variable does not get populated.|||Can you post all of the relevant actual code?
Andrew J. Kelly SQL MVP
<uri@.bwayphoto.com> wrote in message
news:1139251594.154562.102910@.g43g2000cwa.googlegroups.com...
>I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>|||Well, you're going to have to provide DDL and sample data... what you've
provided so far is not enough to give you an answer.
See http://www.aspfaq.com/5006
<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
>|||<uri@.bwayphoto.com> wrote in message
news:1139251923.570483.247690@.o13g2000cwo.googlegroups.com...
> The @.itemname variable does not get populated.
Maybe you have NULLs in that column.|||Thanks Andrew, I removed the non-relevant code and it worked.
I had some other bug.
Thanks again.|||*doh*
<uri@.bwayphoto.com> wrote in message
news:1139253728.769033.207820@.g14g2000cwa.googlegroups.com...
> Thanks Andrew, I removed the non-relevant code and it worked.
> I had some other bug.
> Thanks again.
>|||I tried this and works fine:
-- CREATE TEST TABLE
create table items
(
itemid int
, itemname varchar(50)
)
-- POPULATE TEST TABLE
insert into items
values (1, 'Item1')
insert into items
values (2, 'Item2')
insert into items
values (3, 'Item3')
-- BEGIN SCRIPT
declare @.itemid int
, @.itemname varchar(50)
DECLARE Itemid_Cursor CURSOR FOR
select distinct itemid,itemname from items
OPEN Itemid_Cursor
FETCH NEXT FROM Itemid_Cursor
INTO @.itemid,@.itemname
while @.@.fetch_status = 0
begin
select @.itemid,@.itemname
FETCH NEXT FROM Itemid_Cursor INTO @.itemid,@.itemname
end
close Itemid_Cursor
deallocate Itemid_Cursor
-- DROP TEST TABLE
drop table items
"uri@.bwayphoto.com" wrote:

> I can not assign more than one variable using this cursor:
>
> DECLARE Itemid_Cursor CURSOR FOR
> select distinct itemid,itemname from items
> OPEN Itemid_Cursor
> FETCH NEXT FROM Itemid_Cursor
> INTO @.itemid,@.itemname
>