Understanding SQL Server User-Defined Functions (UDFs)

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4663 red flag

In this Article we will learn about User-Defined Functions (UDFs) in Sql Server.

Introduction

There are three Types of UDFS in Sql Server:

  1. Scalar
  2. Inline Table-Valued
  3. Multi-statement Table-Valued

1.Scalar User-Defined Function

A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.

Example 1: Here we are creating a Scalar UDF SumTwoNumbers which accepts two input parameters @a and @b and returns output as the sum of the two input parameters.

CREATE FUNCTION SumTwoNumbers
 (
 @a int,
 @b int
 )
 RETURNS int
 AS
 BEGIN
 RETURN @a + @b
 END

Once the above function is created we can use this function as below:

PRINT dbo.SumTwoNumbers(10,20)
 
OR
 
SELECT dbo.SumTwoNumbers(30,20)

Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are using dbo.SumTwoNumbers.

But PRINT SumTwoNumbers(10,20) is incorrect because it is not using two-part naming convention.

2.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. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

Example: In this example we are creating a Inline table-valued function GetAuthorsByCountry which accepts country as the input parameter and returns firstname and lastname of all the authors belonging to the input country.

CREATE FUNCTION GetAuthorsByCountry
 ( @country char(2) )
 RETURNS table
 AS
 RETURN (
 SELECT AuthorName
 FROM Authors
 WHERE country=@country
 )
 GO

We can use the below statement to get all the authors in the country INDIA.

SELECT * FROM GetAuthorsByCountry('INDIA');

3.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. After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByCountry which accepts country as the input parameter and returns author id and authorname of all the authors belonging to the input country. If for the input country there are no authors then this UDF will return a record with no au_id column value and firstname as 'No Authors Found'.

CREATE FUNCTION GetAuthorsByCountry
 ( @country char(10) )
 RETURNS
 @AuthorsByCountry table (
 au_id Varchar(12),
 author_name Varchar(20)
 )
 AS
 BEGIN
 
INSERT INTO @AuthorsByCountry
 SELECT  au_id,
 author_name
 FROM Authors
 WHERE country=@country
 
IF @@ROWCOUNT = 0
 BEGIN
 INSERT INTO @AuthorsByCountry
 VALUES ('','No Authors Found')
 END
 
RETURN
 END
 GO

We can use the below statements to get all the authors in the given input state:

SELECT * FROM GetAuthorsByCountry('INDIA');

Conclusion

Hope this article will server the purpose of understanding about User-Defined Functions (UDFs) in Sql Server.Thanks for reading

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)