/******************************************************
 *
 * Name:         table-value-function-multiline.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-03-2013
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about multiline table value functions (tvf).
 *
 ******************************************************/

-- Use the correct database
USE [AdventureWorks2012]
GO

-- 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

-- Test the table value function (TVF)
SELECT * FROM [dbo].[ufnColor2RgbValues] ('Yellow')


--
-- Code bonus, dumping a DMF
-- 

-- Use msdb
use msdb
go

-- Show the internals
sp_helptext 'msdb.[sys].[dm_db_missing_index_columns]'
go