Post reply

alter the output format of a SP

  • March 9, 2006 at 7:59 pm

    #189713

    Hi,

    I just wondering if someone knows the answer to this. im newish to sql but have created this stored procedure to query my database. The script see's which consultant(s) are free on a particular day and then loops through a table of dates to find who is free in the future.

    this brings the results back perfectly. however i am in dreamweaver trying to show the resultset but it only shows the first record. I presume this is to do with the cursor?

    so my question is how do I get back the results in a nice format, one after another, that i can use?

    any help is appreciated!

    CREATE PROCEDURE sp_Test

    as

    DECLARE @colA nvarchar(20)

    DECLARE @MyCursor CURSOR

    DECLARE @level varchar(1)

    DECLARE @consultancytypename varchar(20)

    DECLARE @daterequired datetime

    DECLARE @client numeric(9)

    SET @daterequired = '01-01-2006'

    SET @level = '2'

    SET @consultancytypename = 'Scanning'

    SET @client = '3'

    SET NOCOUNT OFF

    SET @MyCursor = CURSOR FAST_FORWARD

    FOR

    SELECT DayOfWeekDate

    FROM WeekEndsAndHolidays

    OPEN @MyCursor

    FETCH NEXT FROM @MyCursor

    INTO @ColA

    WHILE @@FETCH_STATUS = 0

    BEGIN

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

    DECLARE @dayname varchar(20)

    DECLARE @dateandday varchar(25)

    SET @dayname = (SELECT dayname from WeekEndsAndHolidays where DayOfWeekDate = @ColA)

    SET @dateandday = LEFT(@ColA,(LEN(@ColA)-8)) + ' (' + @dayname + ')'

    SELECT consultantname 'Name Of Consultant(s)', @dateandday

    FROM vw_consultant_ability

    WHERE (

    --consultancy type is the desired one

    consultancytypename = @consultancytypename

    --the consultants competence is high enough

    AND consultancytypelevel >= @level

    --check they are not on holiday

    AND consultantid NOT IN

    (

    SELECT consultantid

    FROM consultant_availability

    WHERE dateunavailable = @ColA

    )

    )

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

    FETCH NEXT FROM @MyCursor

    INTO @ColA

    END

    CLOSE @MyCursor

    DEALLOCATE @MyCursor

    GO

  • March 9, 2006 at 9:24 pm

    #625845

    When you run your SP under query analyser you would see multiple result set return. The is partly due to the cursor approach that you take to retrieve your data. You can still stick to your current cursor approach and create a temp table to store the records and select the records from the temp table at the end of the SP.

    A better approach is to change it to set based. You should be able to get what you want by selecting from WeekEndsAndHolidays and vw_consultant_ability

  • March 9, 2006 at 9:28 pm

    #625846

    thanks for the advice. think i should be able to sort it now

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

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