{"id":3754,"date":"2013-01-05T21:27:14","date_gmt":"2013-01-05T21:27:14","guid":{"rendered":"http:\/\/craftydba.com\/?p=3754"},"modified":"2013-01-05T22:32:22","modified_gmt":"2013-01-05T22:32:22","slug":"multiline-table-value-functions","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3754","title":{"rendered":"Multiline Table Value Functions"},"content":{"rendered":"

\"\"<\/a>
\nToday, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL<\/a>) or Common Language Runtime (CLR<\/a>) routine that accepts parameters, performs an action, and returns the result of that action as a value. <\/p>\n

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.<\/p>\n

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

I will be using the Adventure Works <\/a>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.<\/p>\n

\r\n-- Use the correct database\r\nUSE [AdventureWorks2012]\r\n\r\n-- Delete existing table value function\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects\r\n    WHERE object_id = OBJECT_ID(N'[dbo].[ufnColor2RgbValues]') AND [type] in (N'TF')\r\n\t)\r\nDROP FUNCTION [dbo].[ufnColor2RgbValues]\r\nGO\r\n\r\n-- Create new table value function\r\nCREATE FUNCTION [dbo].[ufnColor2RgbValues] (@Name nvarchar(15))\r\nRETURNS @RgbValues TABLE \r\n(\r\n    [Name] nvarchar(15) NOT NULL, \r\n    [Red] int NULL, \r\n    [Green] int NULL, \r\n    [Blue] int NULL,\r\n    [HexCode] varbinary(3)\r\n)\r\nAS \r\nBEGIN\r\n\r\n        -- Local variable\r\n\tDECLARE @Var_Name nvarchar(15);\r\n\tSET @Var_Name = LOWER(@Name);\r\n\r\n\t-- Convert name to RGB values\r\n\tIF @Var_Name = 'black'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 0, 0x000000);\r\n\tELSE IF @Var_Name = 'blue'  \r\n \t    INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 255, 0x0000FF);\r\n\tELSE IF @Var_Name = 'grey'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 128, 128, 128, 0x808080);\r\n\tELSE IF @Var_Name = 'red'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 255, 0, 0, 0xFF0000);\r\n\tELSE IF @Var_Name = 'silver'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 224, 224, 224, 0xE0E0E0);\r\n\tELSE IF @Var_Name = 'white'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 255, 255, 255, 0xFFFFFF);\r\n\tELSE IF @Var_Name = 'yellow'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 255, 255, 0, 0xFFFF00);\r\n\tELSE IF @Var_Name = 'silver\/black'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 64, 64, 64, 0x404040);\r\n\tELSE IF @Var_Name = 'multi'  \r\n\t    INSERT INTO @RgbValues VALUES (@Var_Name, 0, 0, 0, 0x000000);\r\n\r\n        -- Nothing left to do\r\n\tRETURN;\r\nEND\r\nGO\r\n\r\n<\/span><\/pre>\n
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 .<\/p>\n
The code snippet below returns the Red Green Blue (RGB) properties for the color named ‘Yellow’.<\/p>\n
-- Example use of the function\r\nSELECT * FROM [dbo].[ufnColor2RgbValues] ('Yellow')\r\nGO\r\n\r\n<\/span><\/pre>\n
\"\"<\/a><\/p>\n
Unlike its INLINE cousin, the MULTILINE table variable function (TVF) is strongly typed and can be coded to do multiple tasks.  <\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
Next time, I am going to go over the two APPLY<\/a> operators that can be used to add in Table Valued Function result sets to a SELECT statement.<\/p>\n
Multiline Table Value Function Example<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[565,31,564,12,15,701,28,29],"class_list":["post-3754","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-create-funtion","tag-database-developer","tag-drop-function","tag-free-code","tag-john-f-miner-iii","tag-multiline-table-value-function","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3754","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3754"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3754\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}