User-Defined Functions in SQL Server

In this blog, I will explain SQL user-defined functions

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

CREATE FUNCTION FN_GETFULLNAME(@FNAME NVARCHAR(100),@LANME NVARCHAR(100))
RETURNS NVARCHAR(200)
AS
BEGIN
RETURN (SELECT @FNAME + ' ' + @LANME AS 'FULLNAME');
END;

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

CREATE FUNCTION FN_TABLE()
RETURNS TABLE
AS
RETURN (SELECT * FROM DETAILS);

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

CREATE FUNCTION FN_MTABLE()
RETURNS @EMP TABLE(
SNO INT,
SNAME NVARCHAR(100)
)
AS
BEGIN
INSERT INTO @EMP VALUES(1,'TEST');
RETURN
END;
SELECT * FROM DBO.FN_MTABLE()