1. Membuat Store Procedure sebagai menampilkan data
CREATE PROCEDURE sp_EmployeeDept
@ID NVARCHAR(20)
AS
BEGIN
SELECT NIK, A.Name AS NAMA, Addreess AS Alamat, City as Kota,
Salary as Gaji, B.Name as Dept, C.DESCRIPTION as Agama,
(case when gender = '1' then 'Pria' else 'Wanita' end) as Jenis_Kelamin
FROM t_Employees A inner join t_Departments B
on A.department = B.ID
inner join RELIGION C
on A.religion = C.ID
WHERE department = @ID
END
@ID NVARCHAR(20)
AS
BEGIN
SELECT NIK, A.Name AS NAMA, Addreess AS Alamat, City as Kota,
Salary as Gaji, B.Name as Dept, C.DESCRIPTION as Agama,
(case when gender = '1' then 'Pria' else 'Wanita' end) as Jenis_Kelamin
FROM t_Employees A inner join t_Departments B
on A.department = B.ID
inner join RELIGION C
on A.religion = C.ID
WHERE department = @ID
END
exec sp_EmployeeDept @ID = '1'
2. Membuat Store Procedure sebagai insert data
CREATE PROCEDURE sp_InsertDept
@ID INT,
@Name varchar(20),
@Tot_employee INT
AS
BEGIN
Insert into t_Departments (ID, Name, Tot_Employee)
Values (@ID, @Name, @Tot_employee)
END
exec sp_InsertDept @ID = '7', @Name = 'Gudang', @Tot_employee = '0'