/*============================================================================
  File:     tvp_client_setup.sql

  Summary:  This script sets up the database and procedures for the 
			TVPParms client demo.

  Date:     August 2008

  SQL Server Version: 10.0.1600.22 (RTM)
------------------------------------------------------------------------------
  Written by Bob Beauchemin, SQLskills.com

  For more scripts and sample code, check out http://www.SQLskills.com

  This script is intended only as a supplement to SQL Server Jumpstart program.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

use master
go
if DATABASEPROPERTY('test', 'Version') is not null
  begin
	alter database test set single_user with rollback immediate

	drop database test
	end
go

create database test
go

use test
go

-- create type 
create type dbo.JobsTable as table (
  job_id int,
  job_desc varchar(50),
  min_lvl tinyint,
  max_lvl tinyint
)
go

create table dbo.JobsClone (
  job_id smallint identity primary key,
  job_desc varchar(50),
  min_lvl tinyint,
  max_lvl tinyint
)
go

if object_id('InsertJobs') is not null
	drop procedure InsertJobs	
go

create procedure InsertJobs (@tvp1 dbo.JobsTable readonly)
as
INSERT INTO dbo.JobsClone (job_desc, min_lvl, max_lvl)
  SELECT job_desc, min_lvl, max_lvl from @tvp1
go

SELECT * FROM dbo.JobsClone
