USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TVPDEMO')
	DROP DATABASE [TVPDEMO]
GO

CREATE DATABASE [TVPDEMO]
GO

USE [TVPDEMO] 
GO

CREATE TYPE udt_HospitalHierarchy AS TABLE ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
						name varchar(30) not null,
						[type] char(10) not null
						)
GO

-- New Catalog View sys.table_types 
SELECT * FROM sys.table_types 
GO

-- Can't create a table type with a computed column
CREATE TYPE udt_HospitalHierarchy_with_level AS TABLE ( hid HierarchyId NOT NULL,
						[Level] AS hid.GetLevel() ,
						name varchar(30) not null,
						[type] char(10) not null
						)
GO


-- Error 2785 

-- Create procedure using a table-valued paremeter
-- Parameter has to be READONLY
CREATE PROCEDURE dbo.Get_Residents_From_Table_Valued_Parameter ( 
@tvp udt_HospitalHierarchy READONLY 
)
AS
		SELECT hid.ToString() AS node,
			hid.GetLevel() AS [level],
			name,
			[type]
		FROM @tvp
		WHERE [type]  = 'Resident'
GO

-- Create a table variable using the UDT
DECLARE @h1 udt_HospitalHierarchy

-- Insert using a ROW constructor (new feature of 2008)
INSERT @h1 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
	'Seattle Grace Hospital',
	'Hospital' ),
	( hierarchyid::Parse('/1/'),
	'Cardiology',
	'Department' ),
	( hierarchyid::Parse('/1/1/'),
	'Preston Burke',
	'Consultant' ),
	( hierarchyid::Parse('/1/2/'),
	'Cristina Yang',
	'Resident' ),
	( hierarchyid::Parse('/1/3/'),
	'Erica Hahn ',
	'Consultant' )

/* Just show what is in there! */
SELECT hid.ToString() AS node,
		hid.GetLevel() AS [level],
		name,
		[type]
FROM @h1

/* Execute the procedure */
EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h1
GO

/*
And to prove it is adaptable use a different table variable and pass that to the procedure:
*/
DECLARE @h2 udt_HospitalHierarchy

INSERT @h2 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
	'Seattle Grace Hospital',
	'Hospital' ),
	( hierarchyid::Parse('/1/'),
	'General Surgery',
	'Department' ),
	( hierarchyid::Parse('/1/1/'),
	'Richard Webber',
	'Consultant' ),
	( hierarchyid::Parse('/1/2/'),
	'Miranda Bailey',
	'Consultant' ),
	( hierarchyid::Parse('/1/3/'),
	'Meredith Grey ',
	'Resident' )

SELECT hid.ToString() AS node,
	hid.GetLevel() AS [level],
	name,
	[type]
FROM @h2

EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h2
GO


/*
-- Clean up data in dbo.HospitalHierarchy 
DELETE FROM dbo.HospitalHierarchy 
WHERE 		hid.GetLevel() > 0

-- Clean up database only
DROP TYPE udt_HospitalHierarchy 
DROP PROCEDURE dbo.Get_Residents_From_Table_Valued_Parameter 

-- Database
USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TVPDEMO')
	DROP DATABASE [TVPDEMO]
GO
*/