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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<span style="color: #008000;">-- Use the correct database USE [AdventureWorks2012] GO -- Delete existing scalar valued function IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetStock]') AND [type] in (N'FN') ) DROP FUNCTION [dbo].[ufnGetStock] GO -- Create stub scalar valued function CREATE FUNCTION [dbo].[ufnGetStock] (@ProductID [int]) RETURNS [int] AS BEGIN RETURN 1; END GO -- Alter sclar valued function ALTER FUNCTION [dbo].[ufnGetStock] (@ProductID [int]) RETURNS [int] AS BEGIN -- Declare local variable DECLARE @ret int; -- Only look at inventory in the misc storage SELECT @ret = SUM(p.[Quantity]) FROM [Production].[ProductInventory] p WHERE p.[ProductID] = @ProductID AND p.[LocationID] = '6'; -- Return the result IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END GO </span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
<span style="color: #008000;">-- Example use of the function SELECT ProductId, [name] as ProductName, [dbo].[ufnGetStock](ProductId) as StockLevel FROM [Production].[Product] WHERE [name] like '%seat%'; </span> |
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).
