An Introduction of User Define Function in SQL

In this article I am going to explain about how to create User Define Function in SQL.
  • 2172

Introduction

In SQL Server there are two type of function which are used to manipulate data in database.

  • System define function
  • User define function

SQL server provide a list of system define function which are used to manipulate data. These system define function are predefine. We can't change functionality of these system define function. There are many situation where we need a function which performer some specific task according to our need. There function are known as user define function.

There are three types of user define function in SQL Server.

  • Scalar- value function
  • Simple table-valued function
  • Multi-statement table-valued function

Scalar- value function
Scalar- value function is used to return a single value of any T-SQL data type.

Simple table-valued function
Simple table-valued function is used returns a table that's based on single SELECT statement.

Multi-statement table-valued function
Multi-statement table-valued function is used to return a table that's based on multiple statement.

A Statement that create mcnemp table

Clipboard02.jpg

Statements that insert data in mcnemp table

Clipboard04.jpg

A Statement that is used to fetch data from mcnemp table

Clipboard06.jpg

A Statement that creates a  function
In this statement we are creating a function.

Clipboard08.jpg

A Statement that invokes the function
In this statement we use function to fetch data from table.

Clipboard10.jpg

Clipboard12.jpg

Clipboard14.jpg

A Statement that creates an other function
In this statement we are creating a second function.

Clipboard16.jpg

A Statement that invokes an other function
In this statement we use function to fetch data from table.

Clipboard18.jpg

Clipboard20.jpg


Categories

More Articles

© 2013 dotNetheaven. All rights reserved.