User-Defined Functions in SQL Server

Functions

In SQL Server, a function is a set of SQL statements that performs a specific task. It is a stored program that accepts input parameters, performs actions and returns value that can be reused.

There are basically 3 types of User-defined Functions

  • Scalar Functions
  • Inline Table-Valued Functions
  • Multi-Statement Table-Valued Functions

Scalar Functions

Scalar function always returns a single(scalar) value. It may or may not have parameters. The returned value can be of any data type, except for text, ntext, cursor, timestamp and image. Scalar Functions can be used in SELECT clause and in WHERE clause.

Syntax:
CREATE FUNCTION Function_Name

(

@Parameter1 data type

,@Parameter….n data type

)
RETURNS return_datatype
AS
BEGIN
Function Body
Return return_value
END;

Calling a scalar user-defined function
SELECT * FROM dbo.FunctionName()

* dbo stands for database owner.

 Inline Table-Valued Functions

Inline Table-Valued Function returns a table instead of single value and can be used to achieve the functionality of parameterized views. It returns a result, much like view, however functions accept parameters whereas view does not support parameters.

Inline Table-Valued Function body has no BEGIN and END block. A Return statement is used with the Select query.

Syntax:
CREATE FUNCTION Function_Name

(

@Parameter1 datatype

, @Parameter 2 datatype

, @Parameter……n datatype

)
RETURNS TABLE
AS
RETURN (Select_Statement)

Calling the Inline Table Valued Function:
SELECT * FROM Function_Name()

Multi-Statement Table-Valued Functions

A Multi-Statement Table-Valued Function returns a table but contains additional T-SQL statements or scripts in Begin and End block. It is used when you need the data from multiple places and return as a single table. Performance wise Inline Table-Valued Functions are much better than a Multi-Statement Table-Valued Function.

Syntax:

CREATE FUNCTION Function_Name()
RETURNS @Parameter Table (column_name1 datatype, column_name2 datatype, column_name n…datatype)
AS
BEGIN
INSERT INTO @Parameter
SELECT column_name1, column_name2, column_name n…
FROM table_name

RETURN
END;

Calling the Multi-statement Table Valued Function:
SELECT * FROM Function_name()

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s