Results 1 to 5 of 5

Thread: Dynamic Cursor

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Dynamic Cursor

    I am trying to use a dynamic cursor in a stored procedure:
    The code looks like this :


    /************************************************** ***
    set @sFormula = 'Monthlyformula'
    set @sStartDate = '02/01/2004'
    set @sEndDate = '02/01/2004'


    exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' )
    /************************************************** ***

    And this is what it is interpreting

    select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004)

    My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned

    Thanks in advance

    SK

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    what happens if you try



    exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @sStartDate +''' and '''+ @sEndDate +''')' )

    or is it

    exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @sStartDate +'' and ''+ @sEndDate +'')' )
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dynamic SQL AND a Cursor..

    Johnny...tell him/her they've won....




    Is this inside a sproc?

    What are you ultimatley trying to do?

    In other words, what action is applied to the cursor rows?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    Doesnt Work

    Originally posted by Paul Young
    what happens if you try



    exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @sStartDate +''' and '''+ @sEndDate +''')' )

    or is it

    exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @sStartDate +'' and ''+ @sEndDate +'')' )

    Either of the Methods deosnt work

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    I am trying to get records from a sql into cursor, in which the sql is determined by the input parameters for that SP. Thats why the sql is determined dynamically

    Thanks

    Originally posted by Brett Kaiser
    Dynamic SQL AND a Cursor..

    Johnny...tell him/her they've won....




    Is this inside a sproc?

    What are you ultimatley trying to do?

    In other words, what action is applied to the cursor rows?

Posting Permissions

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