G
Guest
I have this stored procedure I'm trying to tie to a dataset and it gives me
warnings. But since it runs in SQL server and they are just warnings I
thought it would be ok but when I go to put a form view on the page and bind
it to that this it doesn't work.
Do you know what is wrong with this stored proc to keep it from working with
the form view? The stored proc works fine in SQL server.
CREATE PROCEDURE spTime_HoursSummary
@piEmpID int
AS
begin
declare @iWeek int,
@iMonth int,
@iPrior int,
@iWeekMi int,
@iMonthMi int,
@iPriorMi int,
@iTemp int,
@iUnAccountedHrs int,
@iOpenProj int
set @iWeek = 0
set @iMonth = 0
set @iPrior = 0
set @iWeekMi = 0
set @iMonthMi = 0
set @iPriorMi = 0
set @iTemp = 0
set @iUnAccountedHrs = 0
set @iOpenProj = 0
select @iTemp = w.WEEK
from tblWorkingDays w
where w.WORKING_DAYS = getdate()
If Month(getdate()) = 1
begin -- Accounting for Jan's Prior month is December of the Prior Year
select @iPrior = Sum(t.HOURS), @iPriorMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = 12
and Year(w.WORKING_DAYS) = Year(getdate()) -1
end
else
begin
select @iPrior = Sum(t.HOURS) , @iPriorMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate()) - 1
and Year(w.WORKING_DAYS) = Year(getdate())
end
select @iMonth = Sum(t.HOURS) , @iMonthMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
select @iWeek = Sum(t.HOURS) , @iWeekMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
and w.WEEK = @iTemp
select @iUnAccountedHrs = Sum(w.HOURS) - Sum(t.HOURS)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
select @iOpenProj = count(ea.PROJ_CODE)
from tblEmpAssigned ea inner join tblProject p on ea.PROJ_CODE = p.PROJ_CODE
where ea.EMP_ID = @piEmpID
and (p.PROJ_STATUS = 'A' or p.PROJ_STATUS = 'R')
select @iWeek as WeekHours, @iMonth as MonthHours, @iPrior as
PriorMonthHours, @iWeekMi as WeekMi, @iMonthMi as MonthMi, @iPriorMi as
PriorMonthMi,
@iUnAccountedHrs as UnAccountedHrs, @iOpenProj as OpenProjects
end
GO
warnings. But since it runs in SQL server and they are just warnings I
thought it would be ok but when I go to put a form view on the page and bind
it to that this it doesn't work.
Do you know what is wrong with this stored proc to keep it from working with
the form view? The stored proc works fine in SQL server.
CREATE PROCEDURE spTime_HoursSummary
@piEmpID int
AS
begin
declare @iWeek int,
@iMonth int,
@iPrior int,
@iWeekMi int,
@iMonthMi int,
@iPriorMi int,
@iTemp int,
@iUnAccountedHrs int,
@iOpenProj int
set @iWeek = 0
set @iMonth = 0
set @iPrior = 0
set @iWeekMi = 0
set @iMonthMi = 0
set @iPriorMi = 0
set @iTemp = 0
set @iUnAccountedHrs = 0
set @iOpenProj = 0
select @iTemp = w.WEEK
from tblWorkingDays w
where w.WORKING_DAYS = getdate()
If Month(getdate()) = 1
begin -- Accounting for Jan's Prior month is December of the Prior Year
select @iPrior = Sum(t.HOURS), @iPriorMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = 12
and Year(w.WORKING_DAYS) = Year(getdate()) -1
end
else
begin
select @iPrior = Sum(t.HOURS) , @iPriorMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate()) - 1
and Year(w.WORKING_DAYS) = Year(getdate())
end
select @iMonth = Sum(t.HOURS) , @iMonthMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
select @iWeek = Sum(t.HOURS) , @iWeekMi = Sum(t.Mileage)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
and w.WEEK = @iTemp
select @iUnAccountedHrs = Sum(w.HOURS) - Sum(t.HOURS)
from tblWorkingDays w
left outer join tblTime t on w.WORKING_DAYS = t.SVC_DATE and t.EMP_ID =
@piEmpID
where Month(w.WORKING_DAYS) = Month(getdate())
and Year(w.WORKING_DAYS) = Year(getdate())
select @iOpenProj = count(ea.PROJ_CODE)
from tblEmpAssigned ea inner join tblProject p on ea.PROJ_CODE = p.PROJ_CODE
where ea.EMP_ID = @piEmpID
and (p.PROJ_STATUS = 'A' or p.PROJ_STATUS = 'R')
select @iWeek as WeekHours, @iMonth as MonthHours, @iPrior as
PriorMonthHours, @iWeekMi as WeekMi, @iMonthMi as MonthMi, @iPriorMi as
PriorMonthMi,
@iUnAccountedHrs as UnAccountedHrs, @iOpenProj as OpenProjects
end
GO