User-Defined Functions-VBA functions - MS Access

User-Defined Functions (UDFs) are very similar to VBA functions in that you can incorporate them both within queries (or Views) and inside program code (or stored procedures). When adding them to Views, the same rules apply as in VBA: if you write a vast number of lines of code and execute blocks of SQL, don’t be surprised when you add it to generate a column that it is very slow.
Consider the following SQL example (FunctionExample.sql).

This will not execute in SQL Server because you are not allowed to refer to a calculated expression in another expression (this might come as a big surprise). So, to make this work, you require the following:

One solution to restating the sales tax calculation would be to create the following function and modify the SQL as shown in the example that follows, notice that you need to call the function with the dbo prefix (this is because functions need to be prefixed by the schema that owns the function):

INSIDE OUT Table-valued functions

In addition to having functions called scalar-valued functions that return a single piece of data, functions returning a table of data in a similar manner as a stored procedure are allowed. These are called table-valued functions. These functions also have an advantage over a stored procedure in that they can be inserted in a FROM clause. There is also a subset of these functions known as inline user-defined functions, which can act like a View that accepts parameters, as shown in the following code (TableValued Functions.sql):

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics