数据库 存储过程的所有代码
数据库 存储过程的全部代码
1、/*存储过程spwebAddLeaveReq*/
CREATE PROC DBO.spwebAddLeaveReq
@EmployeeID INT,
@StartTime DATETIME,
@SubmitTime datetime,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblLeave(
EmployeeID,
StartTime,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID ,
@StartTime ,
@SubmitTime,
@EndTime ,
@ApproverID ,
@Hours ,
@Reason )
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddLeaveReq TO BlueHillASPUser
2、/*创建存储过程spwebAddOTReq向表 tblOvertime 插入一条已提交的加班申请*/
CREATE PROC spwebAddOTReq
@EmployeeID INT,--
@Type TINYINT,
@SubmitTime DATETIME,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblOvertime(
EmployeeID,
Type,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID,
@Type,
@SubmitTime,
@EndTime,
@ApproverID,
@Hours,
@Reason)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddOTReq TO BlueHillASPUser
4、/*创建存储过程spwebCancelLeaveReq用来取消表 tblLeave 中的一条请假申请*/
CREATE PROC spwebCancelLeaveReq
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelLeaveReq TO BlueHillASPUser
5、/*创建存储过程spwebCancelOTReq用来取消表tblOvertime中一条加班申请*/
CREATE PROC spwebCancelOTReq
@OvertimeID INT
AS
DELETE tblOvertime
WHERE OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelOTReq TO BlueHillASPUser
6、/*创建存储过程spwebChangeLeaveStatus更新表tblLeave中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeLeaveStatus
@LeaveID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET Status=@Status,DenyReason=@DenyReason
where LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeLeaveStatus TO BlueHillASPUser
7、/*创建存储过程spwebChangeOTStatus更新表tblOvertime中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeOTStatus
@OvertimeID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblOvertime
SET Status=@Status,DenyReason=@DenyReason
where OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeOTStatus TO BlueHillASPUser
8、/*创建存储过程spwebGetCEOInfo可以得到一个 CEOID从TBLEMPLOYEE表中。*/
CREATE PROC spwebGetCEOInfo
@CEOID INT
AS
RETURN @CEOID(SELECT EmployeeID FROM tblEmployee WHERE Title='CEO')
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetCEOInfo TO BlueHillASPUser
9、/*创建存储过程spwebGetCEOInfo可以按指定部门ID
和指定的时间段汇总本部门的员工考勤信息。*/
CREATE PROC spwebGetDeptAttendSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM viwwebAllAttend
WHERE DeptID=@DeptID OR (BreachTime>=@StartTime AND BreachTime<=@EndTime)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptAttendSummary TO BlueHillASPUser
10、/*创建存储过程spwebGetDeptLvSummary可以按指定部门ID
和指定的时间段汇总本部门员工已批准的请假信息。*/
CREATE PROC spwebGetDeptLvSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,L.StartTime,L.EndTime FROM tblLeave AS L
INNER JOIN tblEmployee AS E ON L.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (L.StartTime<=@StartTime AND L.EndTime>=@EndTime) OR L.Status='已批准'
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptLvSummary TO BlueHillASPUser
11、/*创建存储过程spwebGetDeptOTSummary可以按指定部门ID、指定时间段
和指定加班类型汇总本部门员工已批准的加班信息*/
CREATE PROC spwebGetDeptOTSummary
@DeptID INT,
@Type TINYINT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,O.StartTime,O.EndTime FROM tblOvertime AS O
INNER JOIN tblEmployee AS E ON O.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (O.StartTime<=@StartTime AND O.EndTime>=@EndTime)
OR Type=@Type
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptOTSummary TO BlueHillASPUser
12、/*创建存储过程spwebGetEmployeeID根据员工登录名得到员工 ID。*/
CREATE PROC spwebGetEmployeeID
@LoginName CHAR(20),
@EmployeeID INT OUTPUT
AS
SET @EmployeeID=(SELECT EmployeeID FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmployeeID TO BlueHillASPUser
/*执行语句*/
DECLARE @ANSWER INT
EXEC spwebGetEmployeeID 'l1',@ANSWER OUTPUT
SELECT 'SDFGG',@ANSWER
13、/*创建存储过程spwebGetEmpPassword根据员工登录名得到员工密码。*/
CREATE PROC spwebGetEmpPassword
@LoginName CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillWinUser
14、/*创建存储过程spwebGetEmpPassword根据员工ID得到员工登录密码*/
CREATE PROC spwebGetEmpPasswordByID
@EmployeeID CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE EmployeeID=@EmployeeID)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPasswordByID TO BlueHillASPUser
15、/*创建存储过程spwebUpdatePassword根据员工ID
更新表 tblEmployee 中的员工登录密码*/
CREATE PROC spwebUpdatePassword
@EmployeeID INT,
@Password BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@Password
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillWinUser
16、/*创建存储过程spwebUpdateSelfIntro根据员工ID
更新表 tblEmployee 中的员工自我介绍信息*/
CREATE PROC spwebUpdateSelfIntro
@EmployeeID INT,
@SelfIntro CHAR(200)
AS
UPDATE tblEmployee
SET SelfIntro=@SelfIntro
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdateSelfIntro TO BlueHillASPUser
17、/*此存储过程根据输入的参数信息先确定要添加的绩效考核子项
目所属的绩效考核是否存在，如果不存在，就先在表 tblPerformance
中添加一条绩效考核信息，然后再在表 tblPerformItem 中添加要加入的绩效考核子项目。*/
CREATE PROC spwebAddPerformObject
@EmployeeID INT,
@PerformYear INT,
@PerformSeason TINYINT,
@ObjectContent NCHAR(100)
AS
--DECLARE @GETDATE GETDATE
DECLARE @SELECTID INT
SET @SELECTID=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
IF @EmployeeID IN(SELECT EmployeeID FROM tblPerformance)
BEGIN
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID,@ObjectContent)
END
ELSE
BEGIN
INSERT tblPerformance(EmployeeID,SubmitTime,PerformYear,PerformSeason)
VALUES(@EmployeeID,CURRENT_TIMESTAMP,'2006','1')
DECLARE @SELECTID2 INT
SET @SELECTID2=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID2,@ObjectContent)
END
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddPerformObject TO BlueHillASPUser
18、/*创建存储过程spwebDeletePerformItem从表
tblPerformItem 中删除一条指定记录。*/
CREATE PROC spwebDeletePerformItem
@PerformItemID INT
AS
DELETE tblPerformItem
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebDeletePerformItem TO BlueHillASPUser
19、/*创建存储过程spwebGetDeptSalarySummary可以按部门得到指定时间段内
的员工工资汇总信息。*/
CREATE PROC spwebGetDeptSalarySummary
@DeptID INT,
@StartTime DATETIME
AS
SELECT S.SalaryID,S.EmployeeID,S.SalaryTime,
S.BalaryTime,S.OvertimeSalary,S.AbsenseSalary,
S.OtherSalary,D.DeptID FROM tblSalary AS S
INNER JOIN tblEmployeeID AS E ON E.EmployeeID=S.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DepID
WHERE D.DeptID=@DeptID AND S.SararyTime=@StartTime
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptSalarySummary TO BlueHillASPUser
20、/*创建存储过程spwebUpdatePerform根据传入的参数信息来更新表tblPerformance。*/
CREATE PROC spwebUpdatePerform
@PerformID INT,
@SelfComment NCHAR(200),
@RelfComment NCHAR(200),
@SelfScore INT,
@ReviewScore INT
AS
UPDATE tblPerformance
SET PerformID=@PerformID,SelfComment=@SelfComment,
SelfScore=@SelfScore,ReviewScore=@ReviewScore
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerform TO BlueHillASPUser
21、/*创建存储过程spwebUpdatePerformItem根据绩效考核子项目ID，
更新表 tblPerformItem 中的子项目内容。*/
CREATE PROC spwebUpdatePerformItem
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET PerformItemID=@PerformItemID,ObjectContent=@ObjectContent
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItem TO BlueHillASPUser
22、/*创建存储过程spwinAttendanceQuery根据指定的时间段查询员工考勤信息。*/
CREATE PROC spwinAttendanceQuery
@StartDate DATETIME,
@EndDATE DATETIME
AS
SELECT * FROM tblAttendance
WHERE BreachTime>=@StartDate and BreachTime<=@EndDATE
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAttendanceQuery TO BlueHillWinUser
23、/*创建存储过程spwinChangeEmployeeDept根据员工ID和员工所属部门名称，
来更新表 tblEmployee 中的员工部门 ID。*/
CREATE PROC spwinChangeEmployeeDept
@EmployeeID INT,
@DeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangeEmployeeDept TO BlueHillWinUser
24、/*创建存储过程spwinCreateDepartment向表tblDepartment
添加一条新部门信息的记录。*/
CREATE PROC spwinCreateDepartment
@DeptName NCHAR(10),
@Desciption NVARCHAR(50),
@ManagerID INT
AS
INSERT tblDepartment(DeptName,Desciption,ManagerID)
VALUES(@DeptName,@Desciption,@ManagerID)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinCreateDepartment TO BlueHillWinUser
25、/*创建存储过程spwinDeleteDepartment从表 tblDepartment
中删除一个指定的部门，在删除前先判断该部门是否还有员工，
如有员工则不删除该部门并返回，如无任何员工，则删除该部门。*/
CREATE PROC spwinDeleteDepartment
@DeptName CHAR(10)
AS
IF (SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
IN (SELECT DeptID FROM tblEmployee)
BEGIN
RAISERROR('有员工存在这部门',16,1)
END
ELSE
BEGIN
DELETE tblDepartment
WHERE DeptName=@DeptName
END
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteDepartment TO BlueHillWinUser
26、/*创建存储过程spwinDeleteEmployee根据指定的员工 ID
从表 tblEmployee 中删除一条员工记录。*/
CREATE PROC spwinDeleteEmployee
@EmployeeID INT
AS
DELETE tblEmployeeID
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteEmployee TO BlueHillWinUser
27、/*创建存储过程根据指定的请假申请ID从表 tblLeave
中删除一条请假申请记录。*/
CREATE PROC spwinDelLeaveRequest
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDelLeaveRequest TO BlueHillWinUser
28、/*创建存储过程spwinDeptAllEmployee根据部门名称从视图
viwwinEmployeeList中得到本部门员工的详细信息。*/
CREATE PROC spwinDeptAllEmployee
@DeptName NCHAR(10)
AS
SELECT * FROM viwwinEmployeeList
WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptAllEmployee TO BlueHillWinUser
29、/*创建存储过程spwinDeptLeave根根据部门 ID 得到本部门员工的请假信息*/
CREATE PROC spwinDeptLeave
@DeptID INT
AS
SELECT * FROM tblLeave AS L INNER JOIN tblEmployee AS E
ON L.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptLeave TO BlueHillWinUser
30、/*创建存储过程spwinEmpLeave汇总指定员工的请假信息。*/
CREATE PROC spwinEmpLeave
@EmployeeID INT
AS
SELECT * FROM tblLeave
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinEmpLeave TO BlueHillWinUser
31、/*创建存储过程spwinGetAllDepartment从表
tblDepartment中得到所有部门的部门 ID 和部门名称。*/
CREATE PROC spwinGetAllDepartment
AS
SELECT DeptID,DeptName FROM tblDepartment
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetAllDepartment TO BlueHillWinUser
32、/*创建存储spwinGetEmpbyDeptName得到指定部门名称的部门的所有员工的详细信息*/
CREATE PROC spwinGetEmpbyDeptName
@DeptName NCHAR(10)
AS
SELECT * FROM tblEmployee AS E INNER JOIN tblDepartment AS D
ON E.DeptID=D.DeptID
WHERE D.DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetEmpbyDeptName TO BlueHillWinUser
33、/*创建存储spwinMoveEmpBetweenDept完成把一个部门所有员工转移到另一个指定部门。*/
CREATE PROC spwinMoveEmpBetweenDept
@FromDeptName NCHAR(10),
@ToDeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@ToDeptName)
WHERE DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@FromDeptName)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinMoveEmpBetweenDept TO BlueHillWinUser
34、/*创建存储spwinRejectLeaveRequest拒绝一条请假申请。。*/
CREATE PROC spwinRejectLeaveRequest
@LeaveID INT,
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET DenyReason=@DenyReason
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinRejectLeaveRequest TO BlueHillWinUser
35、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID，
更新表 tblPerformItem 中的自我评分。*/
CREATE PROC spwebUpdatePerformItemSelf
@PerformItemID INT,
@SelfScore INT
AS
UPDATE tblPerformItem
SET SelfScore=@SelfScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemSelf TO BlueHillASPUser
36、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID，
更新表 tblPerformItem 中的经理评分。*/
CREATE PROC spwebUpdatePerformItemReview
@PerformItemID INT,
@ReviewScore INT
AS
UPDATE tblPerformItem
SET ReviewScore=@ReviewScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemReview TO BlueHillASPUser
37、/*创建储过程spwebGetSubmittedPerform按指定的年份和季度
汇总指定部门的绩效考核详细信息。*/
CREATE PROC spwebGetSubmittedPerform
@DeptID INT,
@PerformYear INT,
@PerformSeason INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear AND P.PerformSeason=@PerformSeason
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetSubmittedPerform TO BlueHillASPUser
38、/*创建储过程spwebGetDeptPerformSummary按指定部门汇总指定年份
的本部门员工的绩效考核信息。*/
CREATE PROC spwebGetDeptPerformSummary
@DeptID INT,
@PerformYear INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptPerformSummary TO BlueHillASPUser
39、/*创建存储过程spwinOTbyGroup按部门名称汇总本部门的员工加班信息*/
CREATE PROC spwinOTbyGroup
@DeptName NVARCHAR(20)
AS
SELECT * FROM tblOvertime AS O INNER JOIN tblEmployee AS E
ON O.EmployeeID=E.EmployeeID INNER JOIN tblDepartment
AS D ON D.DeptID=E.DeptID WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinOTbyGroup TO BlueHillWinUser
40、/*创建存储过程spwebPerformReviewed把表tblPerformance
中的Status字段更新为 1，表示此条记录已经审核。*/
CREATE PROC spwebPerformReviewed
@PerformID INT
AS
UPDATE tblPerformance
SET Status='1' WHERE PerformID=@PerformID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebPerformReviewed TO BlueHillASPUser
41、/*创建存储过程spwinBasicSalaryByEmpID根据员工编号从表
tblEmployee 中查询得到此员工的基本工资信息。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT BasicSalary FROM tblEmployee
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinBasicSalaryByEmpID TO BlueHillWinUser
42、/*创建存储过程spwinBasicSalaryByEmpID通过联接表 tblEmployee 和 tblSalary，按指定员工编号汇总员工工资的历史记录。
。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT * FROM tblSalary AS S INNER JOIN tblEmployee AS E
ON E.EmployeeID=S.EmployeeID WHERE E.EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSalaryHistoryByEmpID TO BlueHillWinUser
43、/*创建存储过程spwinSetBasicSalary用来设置员工的基本工资。*/
CREATE PROC spwinSetBasicSalary
@EmployeeID INT,
@BasicSalary INT
AS
UPDATE tblSalary
SET BasicSalary=@BasicSalary WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSetBasicSalary TO BlueHillWinUser
44、/*创建存储过程spwinQueryEventbyTime从表 tblSystemEvent
中获取指定时间段内的系统事件。*/
CREATE PROC spwinQueryEventbyTime
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM tblSystemEvent WHERE EventTime>=@StartTime AND
EventTime<=@EndTime
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinQueryEventbyTime TO BlueHillWinUser
45、/*创建存储过程spwinChangePassword根据登录名和旧密码来更新密码*/
CREATE PROC spwinChangePassword
@LoginName NVARCHAR(20),
@OldPassword BINARY(20),
@NewPassword BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@NewPassword WHERE LoginName=@LoginName AND
[Password]=@OldPassword
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangePassword TO BlueHillWinUser
46、/*创建存储过程spwinAddEvent向表tblSystemEvent 添加一条新的系统事件记录。*/
CREATE PROC spwinAddEvent
@Message NVARCHAR(50)
AS
ALTER TABLE tblSystemEvent
ADD Message nvarchar (50)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAddEvent TO BlueHillWinUser
47、/*创建存储过程spwebUpdatePerformItemObj根据绩效考核子项目编号，更新该子项目的项目内容。*/
CREATE PROC spwebUpdatePerformItemObj
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET ObjectContent=@ObjectContent
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemObj TO BlueHillASPUser
CREATE PROC DBO.spwebAddLeaveReq
@EmployeeID INT,
@StartTime DATETIME,
@SubmitTime datetime,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblLeave(
EmployeeID,
StartTime,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID ,
@StartTime ,
@SubmitTime,
@EndTime ,
@ApproverID ,
@Hours ,
@Reason )
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddLeaveReq TO BlueHillASPUser
2、/*创建存储过程spwebAddOTReq向表 tblOvertime 插入一条已提交的加班申请*/
CREATE PROC spwebAddOTReq
@EmployeeID INT,--
@Type TINYINT,
@SubmitTime DATETIME,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblOvertime(
EmployeeID,
Type,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID,
@Type,
@SubmitTime,
@EndTime,
@ApproverID,
@Hours,
@Reason)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddOTReq TO BlueHillASPUser
4、/*创建存储过程spwebCancelLeaveReq用来取消表 tblLeave 中的一条请假申请*/
CREATE PROC spwebCancelLeaveReq
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelLeaveReq TO BlueHillASPUser
5、/*创建存储过程spwebCancelOTReq用来取消表tblOvertime中一条加班申请*/
CREATE PROC spwebCancelOTReq
@OvertimeID INT
AS
DELETE tblOvertime
WHERE OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelOTReq TO BlueHillASPUser
6、/*创建存储过程spwebChangeLeaveStatus更新表tblLeave中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeLeaveStatus
@LeaveID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET Status=@Status,DenyReason=@DenyReason
where LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeLeaveStatus TO BlueHillASPUser
7、/*创建存储过程spwebChangeOTStatus更新表tblOvertime中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeOTStatus
@OvertimeID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblOvertime
SET Status=@Status,DenyReason=@DenyReason
where OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeOTStatus TO BlueHillASPUser
8、/*创建存储过程spwebGetCEOInfo可以得到一个 CEOID从TBLEMPLOYEE表中。*/
CREATE PROC spwebGetCEOInfo
@CEOID INT
AS
RETURN @CEOID(SELECT EmployeeID FROM tblEmployee WHERE Title='CEO')
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetCEOInfo TO BlueHillASPUser
9、/*创建存储过程spwebGetCEOInfo可以按指定部门ID
和指定的时间段汇总本部门的员工考勤信息。*/
CREATE PROC spwebGetDeptAttendSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM viwwebAllAttend
WHERE DeptID=@DeptID OR (BreachTime>=@StartTime AND BreachTime<=@EndTime)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptAttendSummary TO BlueHillASPUser
10、/*创建存储过程spwebGetDeptLvSummary可以按指定部门ID
和指定的时间段汇总本部门员工已批准的请假信息。*/
CREATE PROC spwebGetDeptLvSummary
@DeptID INT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,L.StartTime,L.EndTime FROM tblLeave AS L
INNER JOIN tblEmployee AS E ON L.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (L.StartTime<=@StartTime AND L.EndTime>=@EndTime) OR L.Status='已批准'
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptLvSummary TO BlueHillASPUser
11、/*创建存储过程spwebGetDeptOTSummary可以按指定部门ID、指定时间段
和指定加班类型汇总本部门员工已批准的加班信息*/
CREATE PROC spwebGetDeptOTSummary
@DeptID INT,
@Type TINYINT,
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT D.DeptID,O.StartTime,O.EndTime FROM tblOvertime AS O
INNER JOIN tblEmployee AS E ON O.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (O.StartTime<=@StartTime AND O.EndTime>=@EndTime)
OR Type=@Type
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptOTSummary TO BlueHillASPUser
12、/*创建存储过程spwebGetEmployeeID根据员工登录名得到员工 ID。*/
CREATE PROC spwebGetEmployeeID
@LoginName CHAR(20),
@EmployeeID INT OUTPUT
AS
SET @EmployeeID=(SELECT EmployeeID FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmployeeID TO BlueHillASPUser
/*执行语句*/
DECLARE @ANSWER INT
EXEC spwebGetEmployeeID 'l1',@ANSWER OUTPUT
SELECT 'SDFGG',@ANSWER
13、/*创建存储过程spwebGetEmpPassword根据员工登录名得到员工密码。*/
CREATE PROC spwebGetEmpPassword
@LoginName CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE LoginName=@LoginName)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPassword TO BlueHillWinUser
14、/*创建存储过程spwebGetEmpPassword根据员工ID得到员工登录密码*/
CREATE PROC spwebGetEmpPasswordByID
@EmployeeID CHAR(20),
@Password BINARY(20) OUTPUT
AS
SET @Password=(SELECT [Password] FROM tblEmployee
WHERE EmployeeID=@EmployeeID)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetEmpPasswordByID TO BlueHillASPUser
15、/*创建存储过程spwebUpdatePassword根据员工ID
更新表 tblEmployee 中的员工登录密码*/
CREATE PROC spwebUpdatePassword
@EmployeeID INT,
@Password BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@Password
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillASPUser
/* BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePassword TO BlueHillWinUser
16、/*创建存储过程spwebUpdateSelfIntro根据员工ID
更新表 tblEmployee 中的员工自我介绍信息*/
CREATE PROC spwebUpdateSelfIntro
@EmployeeID INT,
@SelfIntro CHAR(200)
AS
UPDATE tblEmployee
SET SelfIntro=@SelfIntro
where EmployeeID=@EmployeeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdateSelfIntro TO BlueHillASPUser
17、/*此存储过程根据输入的参数信息先确定要添加的绩效考核子项
目所属的绩效考核是否存在，如果不存在，就先在表 tblPerformance
中添加一条绩效考核信息，然后再在表 tblPerformItem 中添加要加入的绩效考核子项目。*/
CREATE PROC spwebAddPerformObject
@EmployeeID INT,
@PerformYear INT,
@PerformSeason TINYINT,
@ObjectContent NCHAR(100)
AS
--DECLARE @GETDATE GETDATE
DECLARE @SELECTID INT
SET @SELECTID=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
IF @EmployeeID IN(SELECT EmployeeID FROM tblPerformance)
BEGIN
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID,@ObjectContent)
END
ELSE
BEGIN
INSERT tblPerformance(EmployeeID,SubmitTime,PerformYear,PerformSeason)
VALUES(@EmployeeID,CURRENT_TIMESTAMP,'2006','1')
DECLARE @SELECTID2 INT
SET @SELECTID2=(SELECT PerformID FROM tblPerformance WHERE EmployeeID=@EmployeeID)
INSERT tblPerformItem(PerformID,ObjectContent)
VALUES(@SELECTID2,@ObjectContent)
END
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddPerformObject TO BlueHillASPUser
18、/*创建存储过程spwebDeletePerformItem从表
tblPerformItem 中删除一条指定记录。*/
CREATE PROC spwebDeletePerformItem
@PerformItemID INT
AS
DELETE tblPerformItem
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebDeletePerformItem TO BlueHillASPUser
19、/*创建存储过程spwebGetDeptSalarySummary可以按部门得到指定时间段内
的员工工资汇总信息。*/
CREATE PROC spwebGetDeptSalarySummary
@DeptID INT,
@StartTime DATETIME
AS
SELECT S.SalaryID,S.EmployeeID,S.SalaryTime,
S.BalaryTime,S.OvertimeSalary,S.AbsenseSalary,
S.OtherSalary,D.DeptID FROM tblSalary AS S
INNER JOIN tblEmployeeID AS E ON E.EmployeeID=S.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DepID
WHERE D.DeptID=@DeptID AND S.SararyTime=@StartTime
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptSalarySummary TO BlueHillASPUser
20、/*创建存储过程spwebUpdatePerform根据传入的参数信息来更新表tblPerformance。*/
CREATE PROC spwebUpdatePerform
@PerformID INT,
@SelfComment NCHAR(200),
@RelfComment NCHAR(200),
@SelfScore INT,
@ReviewScore INT
AS
UPDATE tblPerformance
SET PerformID=@PerformID,SelfComment=@SelfComment,
SelfScore=@SelfScore,ReviewScore=@ReviewScore
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerform TO BlueHillASPUser
21、/*创建存储过程spwebUpdatePerformItem根据绩效考核子项目ID，
更新表 tblPerformItem 中的子项目内容。*/
CREATE PROC spwebUpdatePerformItem
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET PerformItemID=@PerformItemID,ObjectContent=@ObjectContent
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItem TO BlueHillASPUser
22、/*创建存储过程spwinAttendanceQuery根据指定的时间段查询员工考勤信息。*/
CREATE PROC spwinAttendanceQuery
@StartDate DATETIME,
@EndDATE DATETIME
AS
SELECT * FROM tblAttendance
WHERE BreachTime>=@StartDate and BreachTime<=@EndDATE
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAttendanceQuery TO BlueHillWinUser
23、/*创建存储过程spwinChangeEmployeeDept根据员工ID和员工所属部门名称，
来更新表 tblEmployee 中的员工部门 ID。*/
CREATE PROC spwinChangeEmployeeDept
@EmployeeID INT,
@DeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangeEmployeeDept TO BlueHillWinUser
24、/*创建存储过程spwinCreateDepartment向表tblDepartment
添加一条新部门信息的记录。*/
CREATE PROC spwinCreateDepartment
@DeptName NCHAR(10),
@Desciption NVARCHAR(50),
@ManagerID INT
AS
INSERT tblDepartment(DeptName,Desciption,ManagerID)
VALUES(@DeptName,@Desciption,@ManagerID)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinCreateDepartment TO BlueHillWinUser
25、/*创建存储过程spwinDeleteDepartment从表 tblDepartment
中删除一个指定的部门，在删除前先判断该部门是否还有员工，
如有员工则不删除该部门并返回，如无任何员工，则删除该部门。*/
CREATE PROC spwinDeleteDepartment
@DeptName CHAR(10)
AS
IF (SELECT DeptID FROM tblDepartment WHERE DeptName=@DeptName)
IN (SELECT DeptID FROM tblEmployee)
BEGIN
RAISERROR('有员工存在这部门',16,1)
END
ELSE
BEGIN
DELETE tblDepartment
WHERE DeptName=@DeptName
END
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteDepartment TO BlueHillWinUser
26、/*创建存储过程spwinDeleteEmployee根据指定的员工 ID
从表 tblEmployee 中删除一条员工记录。*/
CREATE PROC spwinDeleteEmployee
@EmployeeID INT
AS
DELETE tblEmployeeID
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeleteEmployee TO BlueHillWinUser
27、/*创建存储过程根据指定的请假申请ID从表 tblLeave
中删除一条请假申请记录。*/
CREATE PROC spwinDelLeaveRequest
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDelLeaveRequest TO BlueHillWinUser
28、/*创建存储过程spwinDeptAllEmployee根据部门名称从视图
viwwinEmployeeList中得到本部门员工的详细信息。*/
CREATE PROC spwinDeptAllEmployee
@DeptName NCHAR(10)
AS
SELECT * FROM viwwinEmployeeList
WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptAllEmployee TO BlueHillWinUser
29、/*创建存储过程spwinDeptLeave根根据部门 ID 得到本部门员工的请假信息*/
CREATE PROC spwinDeptLeave
@DeptID INT
AS
SELECT * FROM tblLeave AS L INNER JOIN tblEmployee AS E
ON L.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinDeptLeave TO BlueHillWinUser
30、/*创建存储过程spwinEmpLeave汇总指定员工的请假信息。*/
CREATE PROC spwinEmpLeave
@EmployeeID INT
AS
SELECT * FROM tblLeave
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinEmpLeave TO BlueHillWinUser
31、/*创建存储过程spwinGetAllDepartment从表
tblDepartment中得到所有部门的部门 ID 和部门名称。*/
CREATE PROC spwinGetAllDepartment
AS
SELECT DeptID,DeptName FROM tblDepartment
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetAllDepartment TO BlueHillWinUser
32、/*创建存储spwinGetEmpbyDeptName得到指定部门名称的部门的所有员工的详细信息*/
CREATE PROC spwinGetEmpbyDeptName
@DeptName NCHAR(10)
AS
SELECT * FROM tblEmployee AS E INNER JOIN tblDepartment AS D
ON E.DeptID=D.DeptID
WHERE D.DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinGetEmpbyDeptName TO BlueHillWinUser
33、/*创建存储spwinMoveEmpBetweenDept完成把一个部门所有员工转移到另一个指定部门。*/
CREATE PROC spwinMoveEmpBetweenDept
@FromDeptName NCHAR(10),
@ToDeptName NCHAR(10)
AS
UPDATE tblEmployee
SET DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@ToDeptName)
WHERE DeptID=(SELECT DeptID FROM tblDepartment WHERE DeptName=@FromDeptName)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinMoveEmpBetweenDept TO BlueHillWinUser
34、/*创建存储spwinRejectLeaveRequest拒绝一条请假申请。。*/
CREATE PROC spwinRejectLeaveRequest
@LeaveID INT,
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET DenyReason=@DenyReason
WHERE LeaveID=@LeaveID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinRejectLeaveRequest TO BlueHillWinUser
35、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID，
更新表 tblPerformItem 中的自我评分。*/
CREATE PROC spwebUpdatePerformItemSelf
@PerformItemID INT,
@SelfScore INT
AS
UPDATE tblPerformItem
SET SelfScore=@SelfScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemSelf TO BlueHillASPUser
36、/*创建储过程spwebUpdatePerformItemSelf根据绩效考核子项目 ID，
更新表 tblPerformItem 中的经理评分。*/
CREATE PROC spwebUpdatePerformItemReview
@PerformItemID INT,
@ReviewScore INT
AS
UPDATE tblPerformItem
SET ReviewScore=@ReviewScore
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemReview TO BlueHillASPUser
37、/*创建储过程spwebGetSubmittedPerform按指定的年份和季度
汇总指定部门的绩效考核详细信息。*/
CREATE PROC spwebGetSubmittedPerform
@DeptID INT,
@PerformYear INT,
@PerformSeason INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear AND P.PerformSeason=@PerformSeason
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetSubmittedPerform TO BlueHillASPUser
38、/*创建储过程spwebGetDeptPerformSummary按指定部门汇总指定年份
的本部门员工的绩效考核信息。*/
CREATE PROC spwebGetDeptPerformSummary
@DeptID INT,
@PerformYear INT
AS
SELECT * FROM tblPerformance AS P INNER JOIN tblEmployee AS E
ON P.EmployeeID=E.EmployeeID INNER JOIN tblDepartment AS D
ON D.DeptID=E.DeptID WHERE D.DeptID=@DeptID AND
P.PerformYear=@PerformYear
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptPerformSummary TO BlueHillASPUser
39、/*创建存储过程spwinOTbyGroup按部门名称汇总本部门的员工加班信息*/
CREATE PROC spwinOTbyGroup
@DeptName NVARCHAR(20)
AS
SELECT * FROM tblOvertime AS O INNER JOIN tblEmployee AS E
ON O.EmployeeID=E.EmployeeID INNER JOIN tblDepartment
AS D ON D.DeptID=E.DeptID WHERE DeptName=@DeptName
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinOTbyGroup TO BlueHillWinUser
40、/*创建存储过程spwebPerformReviewed把表tblPerformance
中的Status字段更新为 1，表示此条记录已经审核。*/
CREATE PROC spwebPerformReviewed
@PerformID INT
AS
UPDATE tblPerformance
SET Status='1' WHERE PerformID=@PerformID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebPerformReviewed TO BlueHillASPUser
41、/*创建存储过程spwinBasicSalaryByEmpID根据员工编号从表
tblEmployee 中查询得到此员工的基本工资信息。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT BasicSalary FROM tblEmployee
WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinBasicSalaryByEmpID TO BlueHillWinUser
42、/*创建存储过程spwinBasicSalaryByEmpID通过联接表 tblEmployee 和 tblSalary，按指定员工编号汇总员工工资的历史记录。
。*/
CREATE PROC spwinBasicSalaryByEmpID
@EmployeeID INT
AS
SELECT * FROM tblSalary AS S INNER JOIN tblEmployee AS E
ON E.EmployeeID=S.EmployeeID WHERE E.EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSalaryHistoryByEmpID TO BlueHillWinUser
43、/*创建存储过程spwinSetBasicSalary用来设置员工的基本工资。*/
CREATE PROC spwinSetBasicSalary
@EmployeeID INT,
@BasicSalary INT
AS
UPDATE tblSalary
SET BasicSalary=@BasicSalary WHERE EmployeeID=@EmployeeID
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinSetBasicSalary TO BlueHillWinUser
44、/*创建存储过程spwinQueryEventbyTime从表 tblSystemEvent
中获取指定时间段内的系统事件。*/
CREATE PROC spwinQueryEventbyTime
@StartTime DATETIME,
@EndTime DATETIME
AS
SELECT * FROM tblSystemEvent WHERE EventTime>=@StartTime AND
EventTime<=@EndTime
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinQueryEventbyTime TO BlueHillWinUser
45、/*创建存储过程spwinChangePassword根据登录名和旧密码来更新密码*/
CREATE PROC spwinChangePassword
@LoginName NVARCHAR(20),
@OldPassword BINARY(20),
@NewPassword BINARY(20)
AS
UPDATE tblEmployee
SET [Password]=@NewPassword WHERE LoginName=@LoginName AND
[Password]=@OldPassword
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinChangePassword TO BlueHillWinUser
46、/*创建存储过程spwinAddEvent向表tblSystemEvent 添加一条新的系统事件记录。*/
CREATE PROC spwinAddEvent
@Message NVARCHAR(50)
AS
ALTER TABLE tblSystemEvent
ADD Message nvarchar (50)
/*BlueHillWinUser 具有 EXEC 权限*/
GRANT EXEC ON spwinAddEvent TO BlueHillWinUser
47、/*创建存储过程spwebUpdatePerformItemObj根据绩效考核子项目编号，更新该子项目的项目内容。*/
CREATE PROC spwebUpdatePerformItemObj
@PerformItemID INT,
@ObjectContent NCHAR(100)
AS
UPDATE tblPerformItem
SET ObjectContent=@ObjectContent
WHERE PerformItemID=@PerformItemID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebUpdatePerformItemObj TO BlueHillASPUser