Function

 1. Membuat Function Table

CREATE FUNCTION fn_EmpDept
(
@ID INT
)
RETURNS TABLE
AS
RETURN
(
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 B.ID = @ID
)

SELECT * FROM fn_EmpDept ('4')

2. Membuat Function Scalar

CREATE FUNCTION [dbo].[Fn_NIK]
(
@Dept INT
)
RETURNS varchar(5)
AS
BEGIN
DECLARE @Dept_ID INT
DECLARE @NIK_New varchar(5)
SET @Dept_ID = @Dept
DECLARE @VAL1 varchar(2)
DECLARE @VAL2 varchar(3)
SELECT @VAL1 = LEFT (NAME, 2) FROM t_departments WHERE ID = @Dept_ID
SELECT @VAL2 = COUNT(nik) FROM T_employees WHERE department = @Dept_ID
SET @VAL2 = @VAL2 + 1
SET @NIK_New = @VAL1 + CONVERT(NVARCHAR, RIGHT('000' + CAST(@VAL2 as nvarchar(3)),3))
-- Return the result of the function
RETURN @NIK_New
END

select dbo.fn_nik ('2')