Answer: 1. Scalar Function:
User-defined scalar functions return a single data value of the type defined in the RETURNS clause. If RETURN type is Int, then function should return only Int value.
The return type should not be text, ntext, image, cursor, and timestamp.
2. Table-Valued Functions:
Table valued functions used to return the value in TABLE format.
There are two types: They are
(I) Inline table:
CREATE FUNCTION FNC_INLINEGETEMPDET(@EMPID VARCHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT EmpID, FirstName, LastName, Address
FROM tbl_Employees
WHERE EmpID = @EMPID
)
In the above code I am creating a function with return type 'Table' but I have not created any table explicitly, I am just using returning the output of the select query in table format using 'RETURN' statement. As per the above code if the employeeid is matched no rows will be returned
(II) Multistatement table :
CREATE FUNCTION FNC_MULTIGETEMPDET(@EMPID VARCHAR(10))
RETURNS @tblEMPMaster TABLE
(
EMPID VARCHAR(10),
FirstName VARCHAR(50)
)
AS
BEGIN
IF EXISTS(SELECT EmpID from tbl_EmployeePrim WHERE EmpId = @EMPID)
BEGIN
INSERT INTO @tblEMPMaster
SELECT EmpId, SFirstName
FROM tbl_EmployeePrim
WHERE EmpId = @EMPID
END
ELSE
BEGIN
INSERT INTO @tblEMPMaster
SELECT @EMPID, 'No Records found for the EmpID ' + ISNULL(@EMPID,'NULL')
END
RETURN
END
In the above code I am creating a function with return type TABLE but I created table variable called @tblEMPMaster with two columns such as EMPID,FirstName.
In the body of the function I am checking if employeeid exists, if yes then it inserted the matching record to @tblEMPMaster else it will insert "No records found"
3. System Functions
SQL Server provides many system functions that can be used to perform a variety of operations. They cannot be modified.
Some example are :
COALESCE, ISNULL,CONVERT, ISNUMERIC
etc.,