Results 1 to 8 of 8

Thread: Dynamic Cursor

  1. #1
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    4

    Unanswered: Dynamic Cursor

    Hi,

    I want to create a cursor dynamically to a non determined company\table in Dynamics NAV.

    The NAV Database makes for each table object, for each company a SQL table like Company$Tablename.

    So i would like to run through some tables that are settings and would like to do something like this below:
    Code:
    DECLARE @Company varchar( 50);
    DECLARE @Table varchar( 50);
    DECLARE @EndPoint int;
    SET @Company = 'Company';
    SET @Table = 'Table9';
    DECLARE curData CURSOR LOCAL FAST_FORWARD FOR
    	Select DISTINCT EndPoint
    	from [@Company$@Table]
    OPEN curData
    
    FETCH NEXT FROM curData INTO @EndPoint
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
    	print @EndPoint
    
    	FETCH NEXT FROM curData INTO @EndPoint
    END
    CLOSE curData
    DEALLOCATE curData
    Is this possible to do in some way, because the above way does not work.

    I know i could do this by creating the statement in a varchar variable and run an EXEC on the statement. But is there another way, so i can keep my code more readable?

    I use SSMS2005, but the underlying server is badly enough SQL2000
    Last edited by joachimcarrein; 06-24-09 at 10:24.

  2. #2
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    4
    Is it possible to use something like DECLARE x Cursor FOR EXEC( Statement)

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ideally you wouldn't use cursors and do this set based (where possible)
    Ideally you wouldn't create a new table for each company (in most cases)

    How reversible is all this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    4
    Well, Navision makes the tables like this, and there is nothing we can do about it.

    The thing i would like to do is loop through a filter set that is created within Navision:

    Code:
    		DECLARE curFilters CURSOR LOCAL FAST_FORWARD FOR
    			SELECT FilterString, SqlFieldName
    			FROM [TestCompany$ExportFilter]
    				LEFT JOIN [AllFields]
    				ON NavTable=@EndPoint AND NavFieldID=FieldID
    			WHERE ExportFilterID=@ExportID AND ObjectID=@EndPoint AND FieldID<>0
    		OPEN curFilters
    So i know which table, but i want to create a stored procedure which has a parameter with the company name.

    I run through several other tables because it has recursive filters as well, i just wondered if i could make it something general, to keep my code still readable (instead of all red because it is in a varchar variable)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We can see I suppose.
    What do you do with your cursor? Are you selecting data, or inserting\ modifying\ deleting?
    Are you aware of set based processing and that 99.9% of the time it is preferable to cursors? If not, we can probably work without the cursor. If you really, really know exactly what you are doing and cursors are the only alternative then you have some really nasty coding ahead of you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    4
    The cursor is only selecting.

    I found this works which is what i needed:

    Code:
    DECLARE @Company varchar( 50);
    DECLARE @Table varchar( 50);
    DECLARE @EndPoint int;
    SET @Company = 'Company';
    SET @Table = 'Table9';
    EXEC( 'DECLARE CurData CURSOR FOR SELECT DISTINCT EndPoint FROM [' + @Company + '$' + @Table + ']')
    OPEN curData
    
    FETCH NEXT FROM curData INTO @EndPoint
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
    	print @EndPoint
    
    	FETCH NEXT FROM curData INTO @EndPoint
    END
    CLOSE curData
    DEALLOCATE curData
    i did not know a cursor would also be known outside the exec as well.

  7. #7
    Join Date
    Mar 2009
    Posts
    349
    those navision consultants are so expensive.

  8. #8
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by Thrasy
    those navision consultants are so expensive.
    But worth it!

Posting Permissions

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