Today, I continuing my talk 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. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the later.
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 46 47 48 49 50 51 52 53 54 55 |
<span style="color: #008000;"> -- Use the correct database USE [AdventureWorks2012] -- Delete existing table value function IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnColor2RgbValues]') AND [type] in (N'TF') ) DROP FUNCTION [dbo].[ufnColor2RgbValues] GO -- Create new table value function CREATE FUNCTION [dbo].[ufnColor2RgbValues] (@Name nvarchar(15)) RETURNS @RgbValues TABLE ( [Name] nvarchar(15) NOT NULL, [Red] int NULL, [Green] int NULL, [Blue] int NULL, [HexCode] varbinary(3) ) AS BEGIN -- Local variable DECLARE @Var_Name nvarchar(15); SET @Var_Name = LOWER(@Name); -- Convert name to RGB values IF @Var_Name = 'black' INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 0, 0x000000); ELSE IF @Var_Name = 'blue' INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 255, 0x0000FF); ELSE IF @Var_Name = 'grey' INSERT INTO @RgbValues VALUES (@Var_Name, 128, 128, 128, 0x808080); ELSE IF @Var_Name = 'red' INSERT INTO @RgbValues VALUES (@Var_Name, 255, 0, 0, 0xFF0000); ELSE IF @Var_Name = 'silver' INSERT INTO @RgbValues VALUES (@Var_Name, 224, 224, 224, 0xE0E0E0); ELSE IF @Var_Name = 'white' INSERT INTO @RgbValues VALUES (@Var_Name, 255, 255, 255, 0xFFFFFF); ELSE IF @Var_Name = 'yellow' INSERT INTO @RgbValues VALUES (@Var_Name, 255, 255, 0, 0xFFFF00); ELSE IF @Var_Name = 'silver/black' INSERT INTO @RgbValues VALUES (@Var_Name, 64, 64, 64, 0x404040); ELSE IF @Var_Name = 'multi' INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 0, 0x000000); -- Nothing left to do RETURN; END GO </span> |
Please note that the return type of the function is a strongly typed local TABLE variable. Also, the function definition contains multiple lines of T-SQL. The example above takes in a color (@Name) as input and returns various (@RgbValues) as output .
The code snippet below returns the Red Green Blue (RGB) properties for the color named ‘Yellow’.
|
1 2 3 4 5 |
<span style="color: #008000;">-- Example use of the function SELECT * FROM [dbo].[ufnColor2RgbValues] ('Yellow') GO </span> |
Unlike its INLINE cousin, the MULTILINE table variable function (TVF) is strongly typed and can be coded to do multiple tasks.
Many of the Dynamic Management Functions are either INLINE or MULTILINE table variable function (TVF). Please see the example below that shows the internals of the missing index columns.
For a brief recap, I went over three of the five different ways to define a function. For both the scalar and table valued functions, we can extend the functionality of SQL Server by using the Common Language Runtime (CLR) using assemblies. That is an advanced topic that will not be talked about today.
Next time, I am going to go over the two APPLY operators that can be used to add in Table Valued Function result sets to a SELECT statement.
