-- Unit testing. Create a unit test to check a table's schema.
if SCHEMA_ID('UnitTest') is null
begin
	exec ('create schema UnitTest authorization dbo')
end
go
if OBJECT_ID('UnitTest.UpsertPerson') is null
begin
	exec ('create procedure UnitTest.UpsertPerson as begin select ''foo'' end');
end
go
if OBJECT_ID('UnitTest.UpsertPerson') is not null
begin
	exec ('alter procedure UnitTest.UpsertPerson
	@BusinessEntityID int
	,@FirstName nvarchar(50)=null
	,@LastName nvarchar(50)=null
	as
	begin
	set nocount on;
	
	merge into Person.Person as tgt
	using
	(select @BusinessEntityID as BusinessEntityID
		,@FirstName as FirstName
		,@LastName as LastName
	) as src (BusinessEntityID, FirstName, LastName)
	on src.BusinessEntityID=tgt.BusinessEntityID
	when matched then update
	set tgt.FirstName=src.FirstName
	,tgt.LastName=src.LastName
	when not matched by target then insert
	([BusinessEntityID]
	  ,[PersonType]
	  ,[FirstName]
	  ,[LastName]
	  ,[NameStyle]
	  ,[EmailPromotion]
	  ,[ModifiedDate]
	  ,[IsFemale])
	values
	(src.BusinessEntityID
	,''EM'' --PersonType
	,src.FirstName
	,src.LastName
	,0 --NameStyle
	,0 --EmailPromotion
	,getdate() --ModifiedDate
	,1 -- IsFemale
	);
	end');
end

select
	case when COUNT(*)=0 then 'Success' else 'Failure' end as Status
	,COUNT(*) as PersonCount
from Person.Person
where FirstName='Joe'
and LastName='Public'
	

exec UnitTest.UpsertPerson @BusinessEntityID=1498, @FirstName='Joe', @LastName='Public'

select
	case when COUNT(*)=1 then 'Success' else 'Failure' end as Status
	,COUNT(*) as PersonCount
from Person.Person
where FirstName='Joe'
and LastName='Public'


-- Unit test a stored procedure
if OBJECT_ID('tempdb..#ExpectedResults') is null
begin
	create table #ExpectedResults
	(RecursionLevel smallint not null primary key clustered
	,BusinessEntityID int not null
	,FirstName nvarchar(50) not null
	,LastName nvarchar(50) not null
	,OrganizationNode varchar(30) not null
	,ManagerFirstName nvarchar(50) null
	,ManagerLastName nvarchar(50) null)
end
go
truncate table #ExpectedResults
go

insert into #ExpectedResults
(RecursionLevel
,BusinessEntityID
,FirstName
,LastName
,OrganizationNode
,ManagerFirstName
,ManagerLastName)
exec dbo.uspGetEmployeeManagers @BusinessEntityID=10
go

select
	case when COUNT(*)>0
		then 'Victory'
		else 'Defeat'
		end as Status
	,'Make sure the sproc inserted rows as expected'
from #ExpectedResults
go

/*
Cleanup/Prep

USE [AdventureWorks2008R2]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Person].[FK_BusinessEntityAddress_AddressType_AddressTypeID]') AND parent_object_id = OBJECT_ID(N'[Person].[BusinessEntityAddress]'))
ALTER TABLE [Person].[BusinessEntityAddress] DROP CONSTRAINT [FK_BusinessEntityAddress_AddressType_AddressTypeID]
GO

USE [AdventureWorks2008R2]
GO

/****** Object:  Index [PK_AddressType_AddressTypeID]    Script Date: 10/07/2011 17:13:08 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[AddressType]') AND name = N'PK_AddressType_AddressTypeID')
ALTER TABLE [Person].[AddressType] DROP CONSTRAINT [PK_AddressType_AddressTypeID]
GO


if exists
(
select *
from sys.columns c
inner join sys.types t
on t.user_type_id=c.user_type_id
where c.name='StateProvinceID'
and c.object_id=object_id('Person.StateProvince')
and t.name='int'
)
begin
	alter table Person.AddressType
	alter column AddressTypeID tinyint not null
end



USE [AdventureWorks2008R2]
GO

/****** Object:  Index [PK_AddressType_AddressTypeID]    Script Date: 10/07/2011 17:13:08 ******/
ALTER TABLE [Person].[AddressType] ADD  CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED 
(
	[AddressTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'AddressType', @level2type=N'CONSTRAINT',@level2name=N'PK_AddressType_AddressTypeID'
GO



USE [AdventureWorks2008R2]
GO

ALTER TABLE [Person].[BusinessEntityAddress]  WITH CHECK ADD  CONSTRAINT [FK_BusinessEntityAddress_AddressType_AddressTypeID] FOREIGN KEY([AddressTypeID])
REFERENCES [Person].[AddressType] ([AddressTypeID])
GO

ALTER TABLE [Person].[BusinessEntityAddress] CHECK CONSTRAINT [FK_BusinessEntityAddress_AddressType_AddressTypeID]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing AddressType.AddressTypeID.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'BusinessEntityAddress', @level2type=N'CONSTRAINT',@level2name=N'FK_BusinessEntityAddress_AddressType_AddressTypeID'
GO


delete from Person.Person where BusinessEntityID=1498

if exists
(
	select *
	from sys.indexes
	where name='IX_SpecialOfferProduct_ProductID'
	and object_id=object_id('Sales.SpecialOfferProduct')
)
begin
	drop index Sales.SpecialOfferProduct.IX_SpecialOfferProduct_ProductID
end

if object_id('Person.vPerson') is not null
begin
	exec ('drop view Person.vPerson');
end

if exists
(
	select *
	from sys.indexes
	where object_id=object_id('Production.TransactionHistory')
	and name='IX_TransactionHistory_TransactionDate'
)
begin
	drop index Production.TransactionHistory.IX_TransactionHistory_TransactionDate
end
go

if exists
(
	select *
	from sys.columns
	where object_id=object_id('Person.Person')
	and name='IsFemale'
)
begin
	alter table Person.Person
	drop column IsFemale
end
go

 */