alter the output format of a SP
-
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 @daterequireddatetime
DECLARE @clientnumeric(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
ANDconsultancytypelevel >= @level
--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM consultant_availability
WHEREdateunavailable = @ColA
)
)
--------------END----------------------
FETCH NEXT FROM @MyCursor
INTO @ColA
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
-
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
-
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