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

-- Client demonstration
CREATE TABLE dbo.HospitalHierarchy ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
						name varchar(30) not null,
						[type] char(10) not null
)
GO

INSERT dbo.HospitalHierarchy ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
	'Seattle Grace Hospital',
	'Hospital' )
GO


CREATE PROCEDURE dbo.Get_All_Nodes
AS
		SELECT hid.ToString() AS node,
			hid.GetLevel() AS [level],
			name,
			[type]
		FROM dbo.HospitalHierarchy 
		ORDER BY hid
GO

-- Create procedure using a table-valued paremeter
-- Parameter has to be READONLY
CREATE PROCEDURE dbo.Insert_Residents_From_Table_Valued_Parameter ( 
@tvp udt_HospitalHierarchy READONLY 
)
AS
	INSERT INTO HospitalHierarchy ( hid, name, [type] )
	SELECT hid, name,[type]
	FROM @tvp
GO

SELECT hid.ToString() AS node,
		hid.GetLevel() AS [level],
		name,
		[type]
FROM dbo.HospitalHierarchy 
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
*/