Home > DeveloperSection > Blogs > User Defined Function in SQL Server

User Defined Function in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1762  View(s)
Rate this:

User Defined Function in SQL Server

In this blog, I’m explaining user defined functions in sql server.

There are three Types of User Defined Functions in Sql Server:

·         Scalar

·         Inline Table-Valued

·         Multi-statement Table-Valued

Scalar User Defined Functions

The scalar user defined functions can accept zero to many parameters and will return a single scalar value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types.

Example

CREATE FUNCTION AddTwoNumbers

(

@num1 int,

@num2 int

)

RETURNS int

AS

BEGIN

RETURN @num1 + @num2

END

The above function AddTwoNumbers will accept two numbers, add them and give the result.

You can use either of the two statements to call the fumtion.

PRINT dbo.AddTwoNumbers(10,20)

SELECT dbo.AddTwoNumbers(30,20)

Output

Inline Table-Valued User Defined Function

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement.

Example

 

CREATE FUNCTION GetEMPByDept

(

@dept varchar(10)

)

RETURNS table

AS

RETURN(SELECT * FROM EMP where DEPT=@dept)

GO

The above function GetEMPByDept will accept one parameter @dept and gives the output in tabular format.

 

Call the function using this statement

SELECT * FROM GetEMPByDept('Testing')

Output

Multi-Statement Table-Valued User Defined Function

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned. 

Example

CREATE FUNCTION GetDeptByID

( @id int )

RETURNS

@DEPT table (

ID int,

DEPT Varchar(20)

)

AS

BEGIN

INSERT INTO @DEPT SELECT  EMPID,DEPT FROM EMP WHERE EMPID = @id

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @DEPT VALUES ('','No Authors Found')

END

RETURN

END

GO

The above function GetDeptByID will accept one parameter @id and gives the output in table DEPT.

Call the function using this statement

SELECT * FROM GetDeptByID(2)

Output


Don't want to miss updates? Please click the below button!

Follow MindStick