if exists (select * from sysobjects where id = object_id(N'[dbo].[addContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addContact]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[deleteContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[deleteContact]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[getContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getContact]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[getContacts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getContacts]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[updateContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[updateContact]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Contact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Counters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Counters]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[EMail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EMail]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Phone]
GO

CREATE TABLE [dbo].[Contact] (
	[ID] [int] NOT NULL ,
	[LastName] [varchar] (50) NULL ,
	[FirstName] [varchar] (50) NULL ,
	[Address1] [varchar] (50) NULL ,
	[Address2] [varchar] (50) NULL ,
	[City] [varchar] (30) NULL ,
	[State] [varchar] (20) NULL ,
	[ZipCode] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Counters] (
	[TableName] [varchar] (50) NOT NULL ,
	[LastID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EMail] (
	[ContactID] [int] NOT NULL ,
	[EMailType] [int] NOT NULL ,
	[EMail] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Phone] (
	[ContactID] [int] NOT NULL ,
	[PhoneType] [int] NOT NULL ,
	[Phone] [varchar] (30) NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE addContact @id uniqueidentifier output, @lname varchar(50), @fname varchar(50),
	@add1 varchar(50), @add2 varchar(50), @city varchar (30), @state varchar(30), @zip varchar(20),
	@phome varchar(30), @pwork varchar(30), @pcell varchar(30),
	@emhome varchar(100), @emwork varchar(30)
AS

SELECT @id=LastID 
	FROM Counters 
	WHERE TableName = 'Contact';
SELECT @id = @id + 1;
UPDATE Counters
	SET LastID = @id
	WHERE TableName = 'Contact';

INSERT INTO Contact 
	(ID, lastname, firstname, address1, address2, city, state, zipcode)
	VALUES 
	(@id, @lname, @fname, @add1, @add2, @city, @state, @zip);

IF (Len(@phome) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 0, @phome);
END

IF (Len(@pwork) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 1, @pwork);
END

IF (Len(@pcell) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 2, @pcell);
END

IF (Len(@emhome) > 0) BEGIN
	INSERT INTO EMail
		(ContactID, EMailType, EMail)
		VALUES
		(@id, 0, @emhome);
END

IF (Len(@emwork) > 0) BEGIN
	INSERT INTO EMail
		(ContactID, EMailType, EMail)
		VALUES
		(@id, 1, @emwork)
END

RETURN @id

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE deleteContact @id int
AS
DELETE Contact WHERE ID=@id
DELETE Phone WHERE ContactID=@id
DELETE EMail WHERE ContactID=@id

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE getContact @id uniqueidentifier
AS
SELECT * FROM Contact WHERE ID = @id;
SELECT * FROM Phone WHERE ContactID= @id;
SELECT * FROM EMail WHERE ContactID= @id
RETURN



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE getContacts 
AS
SELECT * FROM Contact
RETURN

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE updateContact @id uniqueidentifier, @lname varchar(50), @fname varchar(50),
	@add1 varchar(50), @add2 varchar(50), @city varchar (30), @state varchar(30), @zip varchar(20),
	@phome varchar(30), @pwork varchar(30), @pcell varchar(30),
	@emhome varchar(100), @emwork varchar(30)
AS

UPDATE Contact
	SET lastname=@lname, firstname=@fname,
		address1=@add1, address2=@add2, city=@city, state=@state, zipcode=@zip
	WHERE ID = @id;

DELETE Phone
	WHERE ContactID = @id;

IF (Len(@phome) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 0, @phome);
END

IF (Len(@pwork) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 1, @pwork);
END

IF (Len(@pcell) > 0) BEGIN
	INSERT INTO Phone
		(ContactID, PhoneType, Phone)
		VALUES
		(@id, 2, @pcell);
END

DELETE EMail
	WHERE ContactID = @id;

IF (Len(@emhome) > 0) BEGIN
	INSERT INTO EMail
		(ContactID, EMailType, EMail)
		VALUES
		(@id, 0, @emhome);
END

IF (Len(@emwork) > 0) BEGIN
	INSERT INTO EMail
		(ContactID, EMailType, EMail)
		VALUES
		(@id, 1, @emwork)
END



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

