Function In SQL Server

Function In SQL Server

Create Function

Syntax
CREATE FUNCTION <Function name> ([Paramters])
RETURNS <Datatype return>
AS
[BEGIN]
	<Statements>
	RETURN <Return Vallue>
[END]

Create Function

Example No Paramenter
CREATE FUNCTION CountDepartment()
RETURNS INT
AS
BEGIN
	RETURN (SELECT COUNT(*) FROM Department);
END
GO

Create Function

Example With Input Parameter
CREATE FUNCTION CountEmployeeByDepartment(@deptId INT)
RETURNS INT
AS
BEGIN
	RETURN (SELECT COUNT(*) FROM Employee WHERE DeptId = @deptId);
END
GO

Create Function Return Table

Example
CREATE FUNCTION GetDepartment()
RETURNS TABLE
AS
	RETURN SELECT * FROM Department;
GO