April 16, 2006 at 11:22 pm
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),
@daterequired datetime,
@client numeric(9)
as
DECLARE @colA datetime
DECLARE @MyCursor CURSOR
SET NOCOUNT ON
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Thedate
FROM WeekEndsAndHolidays
WHERE dayname <> 'Saturday'
AND dayname <> 'Sunday'
AND Thedate >= @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
AND consultancytypelevel >= @level
--check to see whether either party has objections
AND consultantid NOT IN
(
SELECT consultantid
FROM client_consultant_unavailability
WHERE clientid = @client
)
)
--------------END----------------------
FETCH NEXT FROM @MyCursor
INTO @ColA
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
April 17, 2006 at 3:14 am
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
)
April 17, 2006 at 11:14 am
you win!
much simpler than mine...i still have a lot to learn.
thanks for the help,
ben
April 18, 2006 at 10:46 am
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