October 11, 2010 at 6:25 am
Hi Guys
I have the following SP which returns some exceptions if a number of hours is worked more than a set amount.
The SP works fine as a record source for a form which supplies the parameters.
What I would like to do is know how many records it is going to give me so I can put this value into a field on the form to show the user the number of exceptions without them having to open the form to see.
Start of SP,,,,,,,,,,,,,
ALTER Procedure dbo.uspExcPer1
(@iDate datetime, @EmpID int)
AS
Begin
SELECT dbo.DayTimes.EmpID, dbo.DayTimes.iDateB AS EndPer1, dbo.ufnWHrsPer1(dbo.DayTimes.iDateB, dbo.DayTimes.EmpID) AS HrsPer1,
dbo.DayTimes.Mins, dbo.DayTimes.iDate, dbo.DayTimes.SchemeID, dbo.FMSchemes.Scheme,
dbo.Employees.[FIRST NAME] + N' ' + dbo.Employees.[LAST NAME] AS Name, dbo.FMSchemeHours.DrivHourPerMin,
dbo.FMSchemeHours.RestDrivHourPerMin, dbo.FMSchemeHours.TotWorkHrsMin, dbo.DayTimes.Type
FROM dbo.DayTimes INNER JOIN
dbo.Employees ON dbo.DayTimes.EmpID = dbo.Employees.[Emp ID] INNER JOIN
dbo.FMSchemes ON dbo.DayTimes.SchemeID = dbo.FMSchemes.SchemeID INNER JOIN
dbo.FMSchemeHours ON dbo.FMSchemes.SchemeID = dbo.FMSchemeHours.SchemeID AND dbo.ufnWHrsPer1(dbo.DayTimes.iDateB,
dbo.DayTimes.EmpID) > dbo.FMSchemeHours.DrivHourPerMin - dbo.FMSchemeHours.RestDrivHourPerMin
WHERE (dbo.DayTimes.iDate = @iDate) AND (dbo.DayTimes.EmpID = @EmpID)
ORDER BY dbo.DayTimes.iDateB
End;
End Of SP,,,,,,,,,,,,,,,,,,
Any help that you can offer would be gretly appreciated.
Regards
Bruce
October 11, 2010 at 6:39 am
You can capure @@ROWCOUNT right after the query execution and then return it as output parameter or as procedure return value (RETURN @myCountVar).
Hope this helps
Gianluca
-- Gianluca Sartori
October 11, 2010 at 6:51 am
thanks for the quick reply Gianluca
------------------
You can capure @@ROWCOUNT right after the query execution and then return it as output parameter or as procedure return value (RETURN @myCountVar).
Hope this helps
Gianluca
---------------------
I have tried using the @@Rowcount, but when I run the SP i still get the recordset open up showing me the results I would expect to see in the form but not the number of records.
Not real sure about output parameters, where would I put this in the SP and I assume somewhere on my form I would use the Output Parameter.
I know that I am on the right track having already tried the above but do not ahve it quite right somewhere.
Regards
Bruce
October 11, 2010 at 6:57 am
I'm not sure that I understand what you're after.
Can you attach the actual code (or a simplified version of if) so that I can try to tweak it?
-- Gianluca Sartori
October 11, 2010 at 7:23 am
Hi Gianluca
The database that I have written is used for Fatigue Management in the Transport Industry here in Australia.
I have a table called DayTimes which has about 4 fields EmpID, iDateB, TypeOf Work(1 = Resting, 2 = Driving), iDate
Each employee has 96 records per day in the this table with idateb being the date and iDateB being the date and time in 15 min intervals.
I have around 7 sp's which figure out exceptions for about 7 different rules.
The SP that I have posted checks if a driver has worked more than 6 hrs in 6.25 hours.
The SP basically will show 96 records for each driver per day sorted by the iDateB field.
On each line of the recordset I have a function that starts @iDateB and loops back 6.25 hours which is 25 records(25 x 15mins = 6.25 hours) and finds the total amount of driving.
So each line of the SP has a new field with the total hours worked in the prev 6.25 hours.
I have the stored procedure made so that it only displays the records that have the field created by the function if the total time is greater than 6 hours.
Some days the driver may not get any results at all but some days there could be maybe 10 lines per day.
I have created an Access ADP form which supplys the paramaters to the SP that I posted earlier.
What I would like to do is rather than the user have to open an exceptions form to see the 10 exceptions I would like to show them on another form that there are 10 Exceptions in one field for exceeding working hours in 6.25 hours, 9 hours, 11, hours 24 hours etc.
I know how to create a count on a table but unfortunately the result is not obtainable in a table, only via the SP that I have posted.
If some screen shots would help I could attach some, but if this proves to be a bit difficult then I can do without it.
Wanted to report by exception and only show the detailed results if the user wanted to view or print out to disipline driver.
Cheers
Bruce
October 11, 2010 at 7:44 am
You could insert the final results of your procedure into a temporary table (those prefixed with #) and then, based on a parameter, return the number of rows in the temp table, or the temp table contents.
Something like this:
ALTER PROCEDURE myProcedure @rowCountOnly bit
AS
BEGIN
SELECT someFields
INTO #tempTable
FROM someTable
WHERE someCondition
IF @rowCountOnly = 0
BEGIN
SELECT * FROM #tempTable
END
ELSE
BEGIN
SELECT COUNT(*) FROM #tempTable
END
END
-- Gianluca Sartori
October 11, 2010 at 2:37 pm
Thanks Gianluca
I will try this way and insert the results of the SP inot the temp table and then count the records.
Thanks
Bruce
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy