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

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

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

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

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

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

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

CREATE TABLE [dbo].[tblContacts] (
	[pkContactID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkPersonID] [int] NOT NULL ,
	[ContactDate] [datetime] NOT NULL ,
	[Type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Length] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblNotes] (
	[pkNoteID] [int] IDENTITY (1, 1) NOT NULL ,
	[fkPersonID] [int] NOT NULL ,
	[Title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Content] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPeople] (
	[pkPersonID] [int] IDENTITY (50, 1) NOT NULL ,
	[LastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FirstName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CompanyName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePhone] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WorkPhone] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WebPage] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblContacts] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblContacts] PRIMARY KEY  CLUSTERED 
	(
		[pkContactID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblNotes] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblNotes] PRIMARY KEY  CLUSTERED 
	(
		[pkNoteID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPeople] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPeople] PRIMARY KEY  CLUSTERED 
	(
		[pkPersonID]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE dbo.sp_PersonCreate
@LastName varchar(40),
@FirstName varchar(40),
@Title varchar(80),
@CompanyName varchar(80),
@Address varchar(240),
@HomePhone varchar(40),
@WorkPhone varchar(40),
@Fax varchar(40),
@Email varchar(120),
@WebPage varchar(120),
@Notes text
AS
INSERT INTO tblPeople
(
   LastName, 
   FirstName, 
   Title, 
   CompanyName,
   Address,
   HomePhone,
   WorkPhone,
   Fax,
   Email,
   WebPage,
   Notes
)
VALUES
(
   @LastName, 
   @FirstName, 
   @Title, 
   @CompanyName,
   @Address,
   @HomePhone,
   @WorkPhone,
   @Fax,
   @Email,
   @WebPage,
   @Notes
)
RETURN @@IDENTITY


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_PersonDelete
@ID int
AS
DELETE FROM tblPeople
WHERE pkPersonID = @ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_PersonRetrieveByID
@ID int
AS
SELECT *
FROM tblPeople
WHERE pkPersonID = @ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_PersonUpdate
@ID int,
@LastName varchar(40),
@FirstName varchar(40),
@Title varchar(80),
@CompanyName varchar(80),
@Address varchar(240),
@HomePhone varchar(40),
@WorkPhone varchar(40),
@Fax varchar(40),
@Email varchar(120),
@WebPage varchar(120),
@Notes text
AS
UPDATE tblPeople
SET LastName = @LastName, 
   FirstName = @FirstName, 
   Title = @Title, 
   CompanyName = @CompanyName,
   Address = @Address,
   HomePhone = @HomePhone,
   WorkPhone = @WorkPhone,
   Fax = @Fax,
   Email = @Email,
   WebPage = @WebPage,
   Notes = @Notes
WHERE pkPersonID = @ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

