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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment