CREATE PROC [dbo].[SP_SelectInsertUpdateDeleteEmployee]
(
@Empid int,
@EmployeeNo nvarchar(200),
@EmployeeName nvarchar(200),
@Department nvarchar(200),
@JobRole nvarchar(200),
@Salary decimal(18,3),
@DateOfJoining datetime,
@PhoneNo nvarchar(50),
@Email nvarchar(50),
@Sex nchar(20),
@StatementType nvarchar(20)=''
)
AS
Begin
if(@StatementType='Insert')
Begin
Insert into EMP_Employees(EmployeeNo,EmployeeName,Department,Salary,JobRole,DateOfJoining,PhoneNo,Email,Sex)
values(@EmployeeNo,@EmployeeName,@Department,@Salary,@JobRole,@DateOfJoining,@PhoneNo,@Email,@Sex)
End
if(@StatementType='Select')
Begin
Select * from EMP_Employees
End
if(@StatementType='Update')
Begin
Update EMP_Employees
Set EmployeeNo=@EmployeeNo, EmployeeName=@EmployeeName,Department=@Department,Salary=@Salary,JobRole=@JobRole,
DateOfJoining=@DateOfJoining,PhoneNo=@PhoneNo,Email=@Email,Sex=@Sex
where EMPID=@Empid
End
if(@StatementType='Delete')
Delete from EMP_Employees where EMPID=@Empid
End
(
@Empid int,
@EmployeeNo nvarchar(200),
@EmployeeName nvarchar(200),
@Department nvarchar(200),
@JobRole nvarchar(200),
@Salary decimal(18,3),
@DateOfJoining datetime,
@PhoneNo nvarchar(50),
@Email nvarchar(50),
@Sex nchar(20),
@StatementType nvarchar(20)=''
)
AS
Begin
if(@StatementType='Insert')
Begin
Insert into EMP_Employees(EmployeeNo,EmployeeName,Department,Salary,JobRole,DateOfJoining,PhoneNo,Email,Sex)
values(@EmployeeNo,@EmployeeName,@Department,@Salary,@JobRole,@DateOfJoining,@PhoneNo,@Email,@Sex)
End
if(@StatementType='Select')
Begin
Select * from EMP_Employees
End
if(@StatementType='Update')
Begin
Update EMP_Employees
Set EmployeeNo=@EmployeeNo, EmployeeName=@EmployeeName,Department=@Department,Salary=@Salary,JobRole=@JobRole,
DateOfJoining=@DateOfJoining,PhoneNo=@PhoneNo,Email=@Email,Sex=@Sex
where EMPID=@Empid
End
if(@StatementType='Delete')
Delete from EMP_Employees where EMPID=@Empid
End
No comments:
Post a Comment