In this Article we will learn about User-Defined Functions (UDFs) in Sql Server.
Introduction
There are three Types of UDFS in Sql Server:
- Scalar
- Inline Table-Valued
- 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