PinPoint your exact location and show the world where you are

��if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Appointments_AppointmentUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Appointments] DROP CONSTRAINT FK_Appointments_AppointmentUser

GO



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

drop procedure [dbo].[DeleteScheduleItem]

GO



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

drop procedure [dbo].[InsertScheduleItem]

GO



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

drop procedure [dbo].[SelectScheduleDay]

GO



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

drop procedure [dbo].[SelectScheduleItems]

GO



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

drop procedure [dbo].[SelectUser]

GO



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

drop procedure [dbo].[UpdateScheduleItem]

GO



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

drop table [dbo].[AppointmentUsers]

GO



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

drop table [dbo].[Appointments]

GO



CREATE TABLE [dbo].[AppointmentUsers] (

	[UserID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,

	[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Appointments] (

	[AppointmentID] [uniqueidentifier] NOT NULL ,

	[UserID] [uniqueidentifier] NOT NULL ,

	[RecurrencePattern] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[StartDate] [datetime] NULL ,

	[EndDate] [datetime] NULL ,

	[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Address] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Latitude] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[Longitude] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

	[IsIntersection] [bit] NULL 

) ON [PRIMARY]

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE DeleteScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



DELETE FROM	Appointments

WHERE AppointmentID = @AppointmentID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE InsertScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



INSERT INTO Appointments

	(AppointmentID, UserID, RecurrencePattern,  StartDate,  EndDate, Description, Address, City, 

	State, Zip, Latitude, Longitude, IsIntersection)

VALUES

	(@AppointmentID,  @UserID, @RecurrencePattern, @StartDate, @EndDate, @Description, 

	@Address, @City, @State, @Zip, @Latitude, @Longitude, @IsIntersection )

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectScheduleDay



	@UserID		uniqueidentifier,

	@Date			datetime

	

AS



SELECT Appointments.*, AppointmentUsers.FirstName as FirstName FROM 

	Appointments

INNER JOIN AppointmentUsers ON

	Appointments.UserID = AppointmentUsers.UserID

WHERE

	Appointments.UserID = @UserID

AND

	CAST(CONVERT(varchar,StartDate,101) AS DateTime) <= @Date

AND

	CAST(CONVERT(varchar,EndDate,101) + ' 23:59' AS DateTime) >= @Date

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectScheduleItems



	@UserID		uniqueidentifier,

	@StartDate		datetime,

	@EndDate		datetime

	

AS



SELECT * FROM 

	Appointments

WHERE

	UserID = @UserID

AND

	StartDate >= @StartDate

AND

	EndDate <= @EndDate

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE SelectUser



	@UserID		uniqueidentifier

	

AS



SELECT TOP 1 UserID FROM 

	AppointmentUsers

WHERE

	UserID = @UserID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE UpdateScheduleItem



	@AppointmentID	uniqueidentifier,

	@UserID		uniqueidentifier,

	@RecurrencePattern	varchar(2000),

	@StartDate		datetime,

	@EndDate		datetime,

	@Description		varchar(100),

	@Address		varchar(300),

	@City			varchar(50),

	@State			varchar(2),

	@Zip			varchar(10),

	@Latitude		varchar(20),

	@Longitude		varchar(20),

	@IsIntersection		bit



AS



UPDATE 

	Appointments

SET

	RecurrencePattern = @RecurrencePattern, 

	StartDate = @StartDate, 

	EndDate = @EndDate, 

	Description = @Description, 

	Address = @Address,  

	City = @City,

	State = @State, 

	Zip = @Zip, 

	Latitude = @Latitude, 

	Longitude = @Longitude, 

	IsIntersection = @IsIntersection

WHERE

	AppointmentID = @AppointmentID

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO