Scalar Valued Functions

Today, I want to start talking about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value.

The return value can either be a scalar (single) value datatype or a composite (multiple) values table. I am going to concentrate on Scalar Value Functions today.

Like most Data Definition Language (DDL) constructs, a user define function has three operations associated with it: CREATE FUNCTION, ALTER FUNCTION and DROP FUNCTION

I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. The SQL snippet below, drops the function if it exists, creates a new stub function, and alters the function to be identical to the original.

While functions can be very complex, this simple one returns the current stock level given a [ProductId]. What good is a function, if it is not used in a query? The code snippet below returns the stock level of all seats sold by Adventure Works.

In short, Scalar Value Functions are good at taking zero or more values as an input parameters, perform any desired database actions, and returning a single value, output parameter, as a result. Next time, I will be talking about Table Valued Functions (TVF).

Scalar Valued Function Output

Scalar Valued Function Example

Related posts

Leave a Comment