SQL Server 2008
This is a continuation of my last question. Now I'm trying to create a stored procedure, however I can't execute it. When I execute it, an error message displays "Cannot drop the table #MyReport", because it does not exist or you do not have permissions.
Please guide me in the right direction.
Below is my stored Procedure
Create PROCEDURE [dbo].[SEL_MyReport]
(
@employeeid int,
@date1 datetime,
@date2 datetime
)
AS
BEGIN
drop table #MyReport
Create Table #MyReport
(
employeeid int,
name varchar(30),
department varchar(30),
checkTime datetime
)
if (@employeeid > 0)
Begin
INSERT INTO #MyReport (employeeid,name, department, checkTime)
select emp.EmpolyeeID, emp.Name,dep.DeptName,tm.checkTime
from TimeInOut tm
left join Employee emp on emp.EmpolyeeId = tm.EmployeeId
left join Department dep on dep.DeptID = emp.defaultDeptID
where (DATEDIFF(s,@date1,tm.checktime) >=0
and DATEDIFF(s,@date2,tm.checktime)<=0) and emp.employeeID = @employeeid
SELECT
employeeid
,name
,department
,[Time In] = MIN(checkTime)
,[Time Out] = MAX(checkTime)
FROM #MyReport
GROUP BY employeeid,name, department, CAST(checktime AS DATE)
End
Else
Begin
INSERT INTO #MyReport (employeeid,name, department, checkTime)
select emp.EmpolyeeID, emp.Name,dep.DeptName,tm.checkTime
from TimeInOut tm
left join Employee emp on emp.EmpolyeeId = tm.EmployeeId
left join Department dep on dep.DeptID = emp.defaultDeptID
where (DATEDIFF(s,@date1,tm.checktime) >=0
and DATEDIFF(s,@date2,tm.checktime)<=0)
SELECT
employeeid
,name
,department
,[Time In] = MIN(checkTime)
,[Time Out] = MAX(checkTime)
FROM #MyReport
GROUP BY employeeid,name, department, CAST(checktime AS DATE)
End
END
Go
exec SEL_MyReport('639','05/01/2014','05/08/2014')
There is quite a bit I would change - here is the code.
You will notice
@employeeid
> 0) has been replaced by a slightly more verbose WHERE
clause#tables
as far as I can tell, the SELECT
should sufficeUnfortunately, I do not have anything to test against, but you should understand the general impression of it. Further, your date filtering seemed quite strange, so I assumed you may have meant something else - I could be mistaken. Either way, the way the date filtering is now done is SARGable
CREATE PROCEDURE [dbo].[SEL_MyReport]
(
@employeeid INT,
@date1 DATETIME,
@date2 DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
SELECT emp.EmpolyeeID
,emp.Name
,dep.DeptName
,[Time In] = MIN(tm.checkTime)
,[Time Out] = MAX(tm.checkTime)
FROM TimeInOut tm
LEFT
JOIN Employee emp on emp.EmpolyeeId = tm.EmployeeId
LEFT
JOIN Department dep on dep.DeptID = emp.defaultDeptID
WHERE tm.checktime >= @date1
AND tm.checktime <= @date2
/***********************************************************************************************************
* I've assumed what you may be trying to express, above
* You might also want to look at the BETWEEN() operator, remembering that it is inclusive in its behaviour
* (DATEDIFF(s,@date1,tm.checktime) >=0
* AND DATEDIFF(s,@date2,tm.checktime)<=0)
***********************************************************************************************************/
AND (emp.employeeID = @employeeid OR @employeeid <= 0)
GROUP BY emp.EmpolyeeID, emp.name, dep.department, CAST(tm.checktime AS DATE)
END
GO