Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: 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.




    Code:
    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_cursor
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, 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:
    Code:
    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:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

    Thanks man :-)
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •