CREATE DATABASE TestTVP
GO

USE TestTVP
GO

SET NOCOUNT ON
GO

-- Create procedure referencing temporary table which has not been created yet!
CREATE PROCEDURE dbo.Get_Residents_From_Tmp_Table
AS
		SELECT hid.ToString() AS node,
			hid.GetLevel() AS [level],
			name,
			[type]
		FROM #tmphierarchy
		WHERE [type]  = 'Resident'
GO

-- Create temporary table using new HierarchyId data type
CREATE TABLE #tmphierarchy ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
				name varchar(30) not null,
				[type] char(10) not null
			)
GO

INSERT #tmphierarchy ( 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' );
GO

EXEC Get_Residents_From_Tmp_Table
GO

INSERT #tmphierarchy ( hid, name, type)
VALUES	( hierarchyid::Parse('/2/'),
	'General Surgery',
	'Department' ),
	( hierarchyid::Parse('/2/1/'),
	'Richard Webber',
	'Consultant' ),
	( hierarchyid::Parse('/2/2/'),
	'Miranda Bailey',
	'Consultant' ),
	( hierarchyid::Parse('/2/3/'),
	'Meredith Grey ',
	'Resident' );
GO

EXEC dbo.Get_Residents_From_Tmp_Table
GO