SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders.RemoveItemFromOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Orders.RemoveItemFromOrder]
(
	@itmOrderID uniqueidentifier,
	@itmProductID uniqueidentifier
)
AS
	SET NOCOUNT ON


	DELETE FROM
		Items 
	WHERE
		itmOrderID = @itmOrderID AND itmProductID = @itmProductID
		
	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders.AddItemToOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Orders.AddItemToOrder]
(
	@itmOrderID uniqueidentifier,
	@itmProductID uniqueidentifier,
	@itmQuantity smallint
)
AS
	SET NOCOUNT ON


	INSERT INTO Items
	(
		itmOrderID,
		itmProductID,
		itmQuantity
	)
	VALUES
	(
		@itmOrderID,
		@itmProductID,
		@itmQuantity
	)

	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Items]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Items](
	[itmOrderID] [uniqueidentifier] NOT NULL,
	[itmProductID] [uniqueidentifier] NOT NULL,
	[itmQuantity] [smallint] NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
	[itmProductID] ASC,
	[itmOrderID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Products](
	[prdID] [uniqueidentifier] NOT NULL,
	[prdName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[prdID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Orders](
	[ordID] [uniqueidentifier] NOT NULL,
	[ordDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[ordID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders.GetAllItemInOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Orders.GetAllItemInOrder]
(
	@itmOrderID uniqueidentifier
)
AS
	SET NOCOUNT ON


	SELECT
		*
	FROM
		Items
		INNER JOIN
			Products ON prdID = itmProductID		
	WHERE
		itmOrderID = @itmOrderID
		

	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products.AddNew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Products.AddNew]
(
	@prdID uniqueidentifier,
	@prdName nvarchar(50)
)
AS
	SET NOCOUNT ON


	INSERT INTO Products
	(
		prdID,
		prdName
	)
	VALUES
	(
		@prdID,
		@prdName
	)

	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products.Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Products.Update]
(
	@prdID uniqueidentifier,
	@prdName nvarchar(50)
)
AS
	SET NOCOUNT ON


	UPDATE
		Products
	SET
		prdName = @prdName
	WHERE
		prdID = @prdID


	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products.Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Products.Delete]
(
	@prdID uniqueidentifier
)
AS
	SET NOCOUNT ON


	DELETE FROM
		Products 
	WHERE
		prdID = @prdID


	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products.GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Products.GetAll]
AS

	SET NOCOUNT ON

	SELECT
		*
	FROM
		Products




	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders.DeleteOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Orders.DeleteOrder]
(
	@ordID uniqueidentifier
)
AS
	SET NOCOUNT ON


	DELETE FROM
		Orders 
	WHERE
		ordID = @ordID

	
	DELETE FROM
		Items 
	WHERE
		itmOrderID = @ordID
		
	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders.DefineNewOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Orders.DefineNewOrder]
(
	@ordID uniqueidentifier,
	@ordDate smalldatetime
)
AS
	SET NOCOUNT ON


	INSERT INTO Orders
	(
		ordID,
		ordDate
	)
	VALUES
	(
		@ordID,
		@ordDate
	)

	RETURN
' 
END