INTRODUCTION TO USER DEFINED FUNCTION
User Defined Functions
- Module code component stored in database
- Perform action & return results
- Results can be single value ( scalar ) or a results set ( table )
- Can accept parameters
Types of Functions
- Scalar UDF’s – Return single value of desired type
- Table-valued UDF’s – Return results as a table data type
- Built in functions- System code T-SQL function
UDF Benefits
- Modular programming
- Reduce network traffic
- Fast execution
- Can be used within queries
GUIDE LINES
General UDF Usage
- Within DML statements
- As defaults on columns like getDate()
- As check constraints on columns
- Parameterize a view
- Replace a stored procedure that return single result set
Table Valued UDF
- Multi statement definition
- Reference like tables & views
- More flexible than views ( T-SQL Logic)
- Easier to use than stored procedure
Inline UDF’s
- Single statement definition
- Can have scalar and table-valued inline UDF’s
- Scalar UDF’ – replace single return value stored procedure
CREATING & USING USER DEFINED FUNCTIONS
- Create scalar function and use them in query by mentioning namespace
- Data access scalar function
- Inline table valued functions
- Parameterized view from inline table-values function ( select * from view)
- When exits….. where … then case statement
- Before as “with encryption”. Cant modify encrypted functions
Creating Scalar User-Defined Functions
A scalar user-defined function accepts zero or more parameters, and returns a single value. Scalar UDFs are often used for converting or translating a current value to a new value, or performing other sophisticated lookups based on specific parameters. Scalar functions can be used within search, column, and join expressions.
–General Scalar Function
CREATE FUNCTION ufnFormatCurrency(@Amount Money)
Returns VarChar(100)
AS
BEGIN
Return ‘$’ + Convert(VarChar, CONVERT(Money, @Amount),1);
–may b database select statement
End
–Usage
Select
soh.SubTotal as Original,
dbo.ufnFormatCurrency(soh.SubTotal) as SubTotal
from
Sales.SalesOrderHeader soh
Table-Valued Functions
- A table-valued user-defined function returns a row-set instead of a single scalar value.
- You can invoke a table-valued function in the FROM clause of a SELECT statement
- A table-valued function specifies the keyword TABLE in its RETURNS clause.
- Table-valued functions are of two types: inline and multi-statement.
- The two types of table-valued functions return the same thing, and they are also invoked the same way. The only real difference between them is the way the function is written to return the row-set.
–Creating Inline Table-Valued Function
Create Function Sales.ufnStoreYTDSales()
Returns Table
as
Return
&n0123456789012345678901234567890123456789
Tags: User Defined Functions