Introduction

In this blog, I will explain the SQL user-definable functions. User-defined functions accepts 0 or more parameters and return a single data value or table data type.

Types

1. Scalar valued function
2. Table valued function
a. Inline table-valued function
b. Multi statement table-valued function

Scalar Function

A scalar user-defined function returns one of the scalar data types. It can accept 0 or more parameters and return a single value. Text, ntext, images and timestamp data types are not supported. Scalar functions can be used in a WHERE clause of the SQL query.
Syntax
  1. CREATE FUNCTION FN_GETFULLNAME(@FNAME NVARCHAR(100),@LANME NVARCHAR(100))
  2. RETURNS NVARCHAR(200)
  3. AS
  4. BEGIN
  5. RETURN (SELECT @FNAME + ' ' + @LANME AS 'FULLNAME');
  6. END;
  8. SELECT DBO.FN_GETFULLNAME('MAGESH','R');-- EXECUTE

Table-valued Function

A table-valued function is a user-defined function that accepts 0 or more parameters and returns data of a table type.
1. Inline table-valued function
2. Multi statement table-valued function

Inline-Table Valued Function

An inline-table valued Function can only return a single value. There is no need for a BEGIN-END block.
Syntax
  1. CREATE FUNCTION FN_TABLE()
  2. RETURNS TABLE
  3. AS
  4. RETURN (SELECT * FROM DETAILS);
  6. SELECT * FROM DBO.FN_TABLE()—EXECUTE

Multi-Statement Function

The Multi-Statement table-valued function returns table value and multiple SQL statements enclosed in BEGIN-END blocks. The return statement doesn't have a value.
Syntax
  1. CREATE FUNCTION FN_MTABLE()
  2. RETURNS @EMP TABLE(
  3. SNO INT,
  4. SNAME NVARCHAR(100)
  5. )
  6. AS
  7. BEGIN
  8. INSERT INTO @EMP VALUES(1,'TEST');
  9. RETURN
  10. END;
  11. SELECT * FROM DBO.FN_MTABLE()