Like programming languages SQL Server also provides User Defined Functions (UDFs). From SQL Server 2000 the UDF feature was added. UDF is a programming construct that accepts parameters, does actions and returns the result of that action. The result either is a scalar value or result set. UDFs can be used in scripts, Stored Procedures, triggers and other UDFs within a database.

Benefits of UDF

  1. UDFs support modular programming. Once you create a UDF and store it in a database then you can call it any number of times. You can modify the UDF independent of the source code.
  2. UDFs reduce the compilation cost of T-SQL code by caching plans and reusing them for repeated execution.
  3. They can reduce network traffic. If you want to filter data based on some complex constraints then that can be expressed as a UDF. Then you can use this UDF in a WHERE clause to filter data.

Types of UDF

  1. Scalar Functions
  2. Table Valued Functions

Consider the following Student and Subject tables for examples.

Student and Subject tables

1. Scalar Functions

A Scalar UDF accepts zero or more parameters and return a single value. The return type of a scalar function is any data type except text, ntext, image, cursor and timestamp. Scalar functions can be use in a WHERE clause of the SQL Query.

Crating Scalar Function

To create a scalar function the following syntax is used.

  1. CREATE FUNCTION function-name (Parameters)
  2. RETURNS return-type
  3. AS
  4. BEGIN
  5. Statement 1
  6. Statement 2
  7. .
  8. .
  9. Statement n
  10. RETURN return-value
  11. END

Example

Create a function as follows.

  1. CREATE FUNCTION GetStudent(@Rno INT)
  2. RETURNS VARCHAR(50)
  3. AS
  4. BEGIN
  5. RETURN (SELECT Name FROM Student WHERE Rno=@Rno)
  6. END

To execute this function use the following command.

  1. PRINT dbo.GetStudent(1)

Output: Ram

1. Table Valued Functions

A Table Valued UDF accepts zero or more parameters and return a table variable. This type of function is special because it returns a table that you can query the results of a join with other tables. A Table Valued function is further categorized into an “Inline Table Valued Function” and a “Multi-Statement Table Valued Function”.

A. Inline Table Valued Function

An Inline Table Valued Function contains a single statement that must be a SELECT statement. The result of the query becomes the return value of the function. There is no need for a BEGIN-END block in an Inline function.

Crating Inline Table Valued Function

To create a scalar function the following syntax is used.

  1. CREATE FUNCTION function-name (Parameters)
  2. RETURNS return-type
  3. AS
  4. RETURN

Query

Example

  1. CREATE FUNCTION GetAllStudents(@Mark INT)
  2. RETURNS TABLE
  3. AS
  4. RETURN
  5. SELECT *FROM Student WHERE Marks>=@Mark

To execute this function use the following command.

  1. SELECT *FROM GetAllStudents(60)

Output

Output

B. Multi-Statement Table Valued Function

A Multi-Statement contains multiple SQL statements enclosed in BEGIN-END blocks. In the function body you can read data from databases and do some operations. In a Multi-Statement Table valued function the return value is declared as a table variable and includes the full structure of the table to be returned. The RETURN statement is without a value and the declared table variable is returned.

Crating Multi-Statement Table Valued Function

To create a scalar function the following syntax is used.

  1. CREATE FUNCTION function-name (Parameters)
  2. RETURNS @TableName TABLE
  3. (Column_1 datatype,
  4. .
  5. .
  6. Column_n datatype
  7. )
  8. AS
  9. BEGIN
  10. Statement 1
  11. Statement 2
  12. .
  13. .
  14. Statement n
  15. RETURN
  16. END

Example

Crate a function as follows.

  1. CREATE FUNCTION GetAvg(@Name varchar(50))
  2. RETURNS @Marks TABLE
  3. (Name VARCHAR(50),
  4. Subject1 INT,
  5. Subject2 INT,
  6. Subject3 INT,
  7. Average DECIMAL(4,2)
  8. )
  9. AS
  10. BEGIN
  11. DECLARE @Avg DECIMAL(4,2)
  12. DECLARE @Rno INT
  13. INSERT INTO @Marks (Name)VALUES(@Name)
  14. SELECT @Rno=Rno FROM Student WHERE Name=@Name
  15. SELECT @Avg=(Subject1+Subject2+Subject3)/3 FROM Subjects WHERE Rno=@Rno
  17. UPDATE @Marks SET
  18. Subject1=(SELECT Subject1 FROM Subjects WHERE Rno=@Rno),
  19. Subject2=(SELECT Subject2 FROM Subjects WHERE Rno=@Rno),
  20. Subject3=(SELECT Subject3 FROM Subjects WHERE Rno=@Rno),
  21. Average=@Avg
  22. WHERE Name=@Name
  23. RETURN
  24. END

To execute this function use the following command.

  1. SELECT * FROM GetAvg('Ram')

Output

Table