User Defined Functions

Overview:

Functions are the database objects, it contains the set of T-SQL transactions and has set of the rule that differs from stored procedure, Will discuss the difference between functions and stored procedures in next post. There are three types of functions namely Scalar Functions, Inline Table-Valued Functions and Multi-Statement Table-Valued Functions.

Scalar Functions: This function type returns a single value of data type specified in the RETURNS clause. The returned values are of any type except text, ntext, image, cursor, or timestamp.

Example:

CREATE FUNCTION fn_ScalarExample(@Val INT)

RETURNS INT

AS

BEGIN

      SET @Val = @Val*2;

      RETURN @Val;

END

GO

Usage   :

                SELECT dbo.fn_ScalarExample(12)

Result   :

                24

Inline Table-Valued Functions:  In this type there is no transactions & returns a variable of data type table whose value is derived from a single SELECT statement. SELECT scripts are specified immediate to RETURN

Example:

CREATE FUNCTION fn_InlineExample(@Val INT)

RETURNS TABLE

AS

      RETURN (SELECT (@Val*2) AS Val);

GO

Usage   :

                SELECT * FROM dbo.fn_InlineExample(12)

Result   :

                24

Multi-Statement Table-Valued Functions: In this we have to define the structure of table in RETURNS section and through the set of transaction data has to be loaded into the table.

Example:

CREATE FUNCTION fn_MultiStatementExample(@Val INT)

RETURNS @Test TABLE( Val INT)

AS

BEGIN

      INSERT @Test

      SELECT  @Val*2

      RETURN

END

GO

Usage   :

                SELECT * FROM dbo.fn_MultiStatementExample(12)

Result   :

                24