/*=====================================================================

  File:      Cleanup.sql for AdventureWorks SQLCLR Example
  Summary:   Deletes inserted data which was created during testing some basic utilities 
             for the AdventureWorks 
             OLTP database which use server side .NET Framework functionality.
  Date:		 January 23, 2004

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

 This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

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 AdventureWorks
GO

--Individual

DECLARE @ContactID int;
DECLARE @customerID int;
SELECT  @ContactID = co.ContactID, @customerID = c.CustomerID 
	FROM Sales.Customer AS c 
	JOIN Sales.Individual AS i on c.CustomerID = i.CustomerID 
	JOIN Person.Contact AS co on i.ContactID = co.ContactID 
	WHERE co.EmailAddress='kim@smith.org';
DELETE Sales.Individual WHERE ContactID = @ContactID;
DELETE Sales.Customer WHERE CustomerID = @customerID;
DELETE Person.Contact WHERE ContactID = @ContactID;
GO

-- Store Contact

DECLARE @ContactID int;
SELECT  @ContactID = co.ContactID 
	FROM Person.Contact AS co 
	WHERE co.EmailAddress='catherine@smith.org';
DELETE Sales.StoreContact WHERE ContactID = @ContactID;
DELETE Person.Contact WHERE ContactID = @ContactID;
GO

-- Vendor Contact

DECLARE @ContactID int;
SELECT  @ContactID = co.ContactID FROM  Person.Contact AS co WHERE co.EmailAddress='amy@smith.org';
DELETE Purchasing.VendorContact WHERE ContactID = @ContactID;
DELETE Person.Contact WHERE ContactID = @ContactID;
GO

-- Employee Contact

DECLARE @ContactID int;
DECLARE @EmployeeID int;
DECLARE @AddressID int;
SELECT  @ContactID = co.ContactID 
	FROM  Person.Contact AS co 
	WHERE co.EmailAddress='ramona@smith.org';
SELECT @EmployeeID = e.EmployeeID
	FROM HumanResources.Employee AS e
	WHERE ContactID = @ContactID; 
SELECT @AddressID = ea.AddressID
	FROM HumanResources.EmployeeAddress AS ea
	WHERE EmployeeID = @EmployeeID;
DELETE HumanResources.EmployeeAddress 
	WHERE EmployeeID = @EmployeeID AND AddressID = @AddressID;
DELETE Person.Address
	WHERE AddressID = @AddressID;
DELETE HumanResources.EmployeeDepartmentHistory
	WHERE EmployeeID = @EmployeeID;
DISABLE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee];
DELETE HumanResources.Employee WHERE ContactID = @ContactID;
ENABLE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee];
DELETE Person.Contact WHERE ContactID = @ContactID;
GO

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE [name] = N'ContactDataSchemaCollection')
DROP XML SCHEMA COLLECTION Person.ContactDataSchemaCollection;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CreateContact')
DROP PROCEDURE [dbo].[usp_CreateContact];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Contact')
DROP ASSEMBLY Contact;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
DROP FUNCTION ConvertCurrency;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = N'ConversionFunction') 
DROP ASSEMBLY ConversionFunction;
GO

IF EXISTS (SELECT * FROM sys.types WHERE [name] = N'Currency') 
DROP TYPE Currency;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = N'CurrencyType') 
DROP ASSEMBLY CurrencyType;
GO