inserting output of cursor into a temp table?

  • hi,

    I have a rather old incomplete stored procedure i made a while back but i need it again.

    The trouble is when i try and bring the results back into my application i only get the first "lot" of results not all the other cycles of the cursor.

    how do i make this possible? do i create a temporary table and insert the values into there? if so how do i do this and bring back the top 20 records?

    the code is below but isnt formatted but i have put it below anyway. I also uploaded the code to http://pastebin.com/664416

    If anyone can help that would be great as this is killing me!

    many thanks,

    Ben

    CREATE PROCEDURE sp_Test

    @level tinyint,

    @consultancytypename varchar(50),

    @daterequireddatetime,

    @clientnumeric(9)

    as

    DECLARE @colA datetime

    DECLARE @MyCursor CURSOR

    SET NOCOUNT ON

    SET @MyCursor = CURSOR FAST_FORWARD

    FOR

    SELECT Thedate

    FROM WeekEndsAndHolidays

    WHEREdayname <> 'Saturday'

    ANDdayname <> 'Sunday'

    ANDThedate >= @daterequired

    OPEN @MyCursor

    FETCH NEXT FROM @MyCursor

    INTO @ColA

    WHILE @@FETCH_STATUS = 0

    BEGIN

    ---------------BEGIN--------------------

    SELECT consultantname 'Name Of Consultant(s)', @ColA 'Date'

    FROM vw_consultant_ability

    WHERE(

    --consultancy type is the desired one

    consultancytypename = @consultancytypename

    --the consultants competence is high enough

    ANDconsultancytypelevel >= @level

    --check to see whether either party has objections

    ANDconsultantid NOT IN

    (

    SELECTconsultantid

    FROM client_consultant_unavailability

    WHEREclientid = @client

    )

    )

    --------------END----------------------

    FETCH NEXT FROM @MyCursor

    INTO @ColA

    END

    CLOSE @MyCursor

    DEALLOCATE @MyCursor

    GO

  • SELECT consultantname 'Name Of Consultant(s)', Thedate as 'Date'

    FROM WeekEndsAndHolidays, vw_consultant_ability

    WHERE dayname 'Saturday'

    AND dayname 'Sunday'

    AND Thedate >= @daterequired

    AND consultancytypename = @consultancytypename

    AND consultancytypelevel >= @level

    AND consultantid NOT IN

    (

    SELECT consultantid

    FROM client_consultant_unavailability

    WHERE clientid = @client

    )

  • you win!

    much simpler than mine...i still have a lot to learn.

    thanks for the help,

    ben

  • Try this as well.  It may run a little faster.  Use query analyzer "show execution plan" and test against your data to see which runs more efficiently.

    SELECT consultantname 'Name Of Consultant(s)', Thedate as 'Date'

    FROM WeekEndsAndHolidays, vw_consultant_ability ca

    LEFT JOIN client_consultant_unavailability ccu on ca.consultantid = ccu.consultantid

    WHERE dayname IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', ' Friday')

    AND Thedate >= @daterequired

    AND consultancytypename = @consultancytypename

    AND consultancytypelevel >= @level

    AND ccu.consultantid is null

    I made some assumtions about where consultantid exists in the tables WeekEndsAndHolidays, vw_consultant_ability .

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply