User Defined Function is a feature of SQL Server that takes parameters like a programming language functions and returns a result or result set.
With User Defined Functions;
• You can achieve faster results by eliminating the cost of compilation like stored procedures.
• It enables modular programming. You can create a desired function and then call it any number of times from anywhere in your code.
User Defined Function Types
1) Scalar Function
Returns a single value specified in the Return statement, except for the text, ntext, image, cursor, and timestamp data types.
You can see example usage below.
|
1 2 3 4 5 6 |
CREATE FUNCTION MyScalarFunction(@intvalue1 int, @intvalue2 int) RETURNS int AS BEGIN RETURN @intvalue1+@intvalue2 END |
Calling a Scalar Function
|
1 |
SELECT dbo.MyScalarFunction(7,4) total_intvalue |
2) Table-Valued Functions
Returns the result set in the table data type. You may want to read “User Defined Table Type and Table Valued Parameter in SQL Server“.
You can see example usage below.
|
1 2 3 4 5 6 7 8 9 |
USE AdventureWorks2014 GO CREATE FUNCTION MyTableValuedFunction(@ListPrice money) RETURNS TABLE AS RETURN SELECT ProductID,Name,ListPrice FROM Production.Product WHERE ListPrice > @ListPrice |
Calling a Table-Valued Function
|
1 2 3 |
USE AdventureWorks2014 GO SELECT * FROM [dbo].[MyTableValuedFunction](100) |
