842 lines
21 KiB
PL/PgSQL
842 lines
21 KiB
PL/PgSQL
IF OBJECT_ID('dbo.[ItemProperty]', 'U') IS NOT NULL
|
|
DROP TABLE [ItemProperty]
|
|
|
|
IF OBJECT_ID('dbo.[SubCategoryProperty]', 'U') IS NOT NULL
|
|
DROP TABLE [SubCategoryProperty]
|
|
|
|
IF OBJECT_ID('dbo.[CategoryProperty]', 'U') IS NOT NULL
|
|
DROP TABLE [CategoryProperty]
|
|
|
|
IF OBJECT_ID('dbo.[PropertyValue]', 'U') IS NOT NULL
|
|
DROP TABLE [PropertyValue]
|
|
|
|
IF OBJECT_ID('ItemComponent', 'U') IS NOT NULL
|
|
DROP TABLE [ItemComponent]
|
|
|
|
IF OBJECT_ID('dbo.[Item]', 'U') IS NOT NULL
|
|
DROP TABLE [Item]
|
|
|
|
IF OBJECT_ID('dbo.[SubCategory]', 'U') IS NOT NULL
|
|
DROP TABLE [SubCategory]
|
|
|
|
IF OBJECT_ID('dbo.[Category]', 'U') IS NOT NULL
|
|
DROP TABLE [Category]
|
|
|
|
IF OBJECT_ID('dbo.[Property]', 'U') IS NOT NULL
|
|
DROP TABLE [Property]
|
|
|
|
IF OBJECT_ID('dbo.[PropertyType]', 'U') IS NOT NULL
|
|
DROP TABLE [PropertyType]
|
|
|
|
CREATE TABLE [PropertyType]
|
|
(
|
|
[Property Type ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Property Type] VARCHAR(32) NOT NULL UNIQUE,
|
|
|
|
PRIMARY KEY([Property Type ID])
|
|
)
|
|
|
|
INSERT INTO [PropertyType] ([Property Type]) VALUES ('Text')
|
|
INSERT INTO [PropertyType] ([Property Type]) VALUES ('DateTime')
|
|
INSERT INTO [PropertyType] ([Property Type]) VALUES ('Money')
|
|
INSERT INTO [PropertyType] ([Property Type]) VALUES ('Integer')
|
|
|
|
CREATE TABLE [Category]
|
|
(
|
|
[Category ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Category Name] NVARCHAR(512) NOT NULL UNIQUE,
|
|
|
|
PRIMARY KEY([Category ID])
|
|
)
|
|
|
|
INSERT INTO [Category] ([Category Name]) VALUES ('Books')
|
|
INSERT INTO [Category] ([Category Name]) VALUES ('Electronics')
|
|
INSERT INTO [Category] ([Category Name]) VALUES ('Appliances')
|
|
INSERT INTO [Category] ([Category Name]) VALUES ('Kitchenware')
|
|
|
|
CREATE TABLE [SubCategory]
|
|
(
|
|
[Sub Category ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Sub Category Name] NVARCHAR(512) NOT NULL UNIQUE,
|
|
[Category ID] INT NOT NULL,
|
|
|
|
FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
|
|
PRIMARY KEY([Sub Category ID])
|
|
)
|
|
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('PCIe Cards', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Laptops', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Desktops', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('CPUs', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Hard Drives', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Switches', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Routers', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('CPU Coolers', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('USB Devices', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Oscilloscopes', 2)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Heaters', 3)
|
|
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Eletric Kettles', 4)
|
|
|
|
CREATE TABLE [Property]
|
|
(
|
|
[Property ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Name] NVARCHAR(128) NOT NULL,
|
|
[Property Type ID] INT NOT NULL,
|
|
|
|
FOREIGN KEY([Property Type ID]) REFERENCES [PropertyType]([Property Type ID]),
|
|
PRIMARY KEY([Property ID])
|
|
)
|
|
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Lanewidth', 1)
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Battery Watt Hours', 1)
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('ASIN', 1)
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Capacity (GiB)', 4)
|
|
|
|
CREATE TABLE [SubCategoryProperty]
|
|
(
|
|
[Sub Category Property ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Sub Category ID] INT NOT NULL,
|
|
[Property ID] INT NOT NULL,
|
|
|
|
FOREIGN KEY([Sub Category ID]) REFERENCES [SubCategory]([Sub Category ID]),
|
|
FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
|
|
PRIMARY KEY([Sub Category Property ID])
|
|
)
|
|
|
|
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(1, 1)
|
|
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(2, 2)
|
|
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(5, 4)
|
|
|
|
CREATE TABLE [CategoryProperty]
|
|
(
|
|
[Category Property ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Category ID] INT NOT NULL,
|
|
[Property ID] INT NOT NULL,
|
|
|
|
FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
|
|
FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
|
|
PRIMARY KEY([Category Property ID])
|
|
)
|
|
|
|
INSERT INTO [CategoryProperty] ([Category ID], [Property ID]) VALUES (1, 3)
|
|
|
|
CREATE TABLE [Item]
|
|
(
|
|
[Item ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Name] NVARCHAR(2048) NOT NULL,
|
|
[Value] MONEY DEFAULT 0,
|
|
[Bought For] MONEY DEFAULT 0,
|
|
[Date Aquired] DATETIME,
|
|
[Comment] NVARCHAR(3072),
|
|
[Note] NVARCHAR(3072),
|
|
[Sold For] MONEY DEFAULT 0,
|
|
[Sold] BIT DEFAULT 0,
|
|
[Date Sold] DATETIME,
|
|
[Sold To] NVARCHAR(1024), -- Maybe could point to a table of comapies, or contacts
|
|
[Category ID] INT NOT NULL,
|
|
[Sub Category ID] INT DEFAULT 0,
|
|
|
|
FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
|
|
FOREIGN KEY([Sub Category ID]) REFERENCES [SubCategory]([Sub Category ID]),
|
|
PRIMARY KEY([Item ID])
|
|
)
|
|
-- Allows grouping items together, i.e. Alienware Desktop -> CPU, HDDs / SSDs, Motherboard, etc.
|
|
CREATE TABLE [ItemComponent]
|
|
(
|
|
[Item Component ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Item ID] INT NOT NULL,
|
|
[Component Item ID] INT NOT NULL,
|
|
|
|
FOREIGN KEY([Item ID]) REFERENCES [Item]([Item ID]),
|
|
FOREIGN KEY([Component Item ID]) REFERENCES [Item]([Item ID]),
|
|
PRIMARY KEY([Item Component ID])
|
|
)
|
|
|
|
CREATE TABLE [PropertyValue]
|
|
(
|
|
[Property Value ID] INT NOT NULL IDENTITY(1, 1),
|
|
[Item ID] INT NOT NULL,
|
|
[Property ID] INT NOT NULL,
|
|
[Value] NVARCHAR(2048) NOT NULL,
|
|
|
|
FOREIGN KEY([Item ID]) REFERENCES [Item]([Item ID]),
|
|
FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
|
|
PRIMARY KEY([Property Value ID])
|
|
)
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[CategoryExists] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[CategoryExists]
|
|
(
|
|
@Category NVARCHAR(512)
|
|
)
|
|
RETURNS BIT
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @Exists BIT = 0
|
|
|
|
IF EXISTS ( SELECT 1 FROM [Category] WHERE [Category Name] = @Category )
|
|
BEGIN
|
|
SET @Exists = 1;
|
|
END
|
|
|
|
RETURN @Exists;
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[PropertyExists] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[PropertyExists]
|
|
(
|
|
@PropertyName NVARCHAR(512)
|
|
)
|
|
RETURNS BIT
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @Exists BIT = 0
|
|
|
|
IF EXISTS ( SELECT 1 FROM [Property] WHERE [Name] = @PropertyName )
|
|
BEGIN
|
|
SET @Exists = 1;
|
|
END
|
|
|
|
RETURN @Exists;
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[SubCategoryExists] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[SubCategoryExists]
|
|
(
|
|
@SubCategory NVARCHAR(512)
|
|
)
|
|
RETURNS BIT
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @Exists BIT = 0
|
|
|
|
IF EXISTS ( SELECT 1 FROM [SubCategory] WHERE [Sub Category Name] = @SubCategory )
|
|
BEGIN
|
|
SET @Exists = 1;
|
|
END
|
|
|
|
RETURN @Exists;
|
|
END
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[GetCategoryProperties] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[GetCategoryProperties]
|
|
(
|
|
@CategoryID INT
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
SELECT [Property].[Name], [Property].[Property ID], [Category Property ID], [PropertyType].[Property Type ID], [PropertyType].[Property Type]
|
|
FROM [CategoryProperty]
|
|
INNER JOIN [Property] ON
|
|
[Property].[Property ID] = [CategoryProperty].[Property ID]
|
|
INNER JOIN [PropertyType] ON
|
|
[Property].[Property Type ID] = [PropertyType].[Property Type ID]
|
|
WHERE [CategoryProperty].[Category ID] = @CategoryID
|
|
)
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[GetCategorySubCategories] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[GetCategorySubCategories]
|
|
(
|
|
@CategoryID INT
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
SELECT [Sub Category ID] AS ID, [Sub Category Name] AS Name
|
|
FROM [SubCategory]
|
|
WHERE [Category ID] = @CategoryID
|
|
)
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[GetPropertyTypes] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[GetPropertyTypes]
|
|
(
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
SELECT [Property Type ID], [Property Type]
|
|
FROM [PropertyType]
|
|
)
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[GetSubCategoryProperties] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE FUNCTION [dbo].[GetSubCategoryProperties]
|
|
(
|
|
@SubCategoryID INT
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN
|
|
(
|
|
SELECT [Property].[Name], [Property].[Property ID], [Sub Category Property ID], [PropertyType].[Property Type ID], [PropertyType].[Property Type]
|
|
FROM [SubCategoryProperty]
|
|
INNER JOIN [Property] ON
|
|
[Property].[Property ID] = [SubCategoryProperty].[Property ID]
|
|
INNER JOIN [PropertyType] ON
|
|
[Property].[Property Type ID] = [PropertyType].[Property Type ID]
|
|
WHERE [SubCategoryProperty].[Sub Category ID] = @SubCategoryID
|
|
)
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[CreateCategory] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[CreateCategory]
|
|
@Category NVARCHAR(512)
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
IF dbo.CategoryExists(@Category) = 1
|
|
BEGIN;
|
|
THROW 70000, 'Duplicate category names not allowed.', 1;
|
|
END;
|
|
|
|
INSERT INTO [Category] ([Category Name]) VALUES (@Category)
|
|
|
|
SELECT SCOPE_IDENTITY() AS ID
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[CreateCategoryProperty] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[CreateCategoryProperty]
|
|
@PropertyName NVARCHAR(128),
|
|
@PropertyTypeID INT,
|
|
@CategoryID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @PropertyID INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES (@PropertyName, @PropertyTypeID)
|
|
|
|
SET @PropertyID = SCOPE_IDENTITY()
|
|
|
|
INSERT INTO [CategoryProperty] ([Category ID], [Property ID]) VALUES (@CategoryID, @PropertyID)
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[CreateSubCategory] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[CreateSubCategory]
|
|
@SubCategory NVARCHAR(512),
|
|
@CategoryID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
IF dbo.SubCategoryExists(@SubCategory) = 1
|
|
BEGIN;
|
|
THROW 51001, 'Duplicate sub-category names not allowed.', 1;
|
|
END;
|
|
|
|
INSERT INTO [SubCategory] ([Sub Category Name], [Category ID]) VALUES (@SubCategory, @CategoryID)
|
|
|
|
SELECT SCOPE_IDENTITY() AS ID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[CreateSubCategoryProperty] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[CreateSubCategoryProperty]
|
|
@PropertyName NVARCHAR(128),
|
|
@PropertyTypeID INT,
|
|
@SubCategoryID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @PropertyID INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES (@PropertyName, @PropertyTypeID)
|
|
|
|
SET @PropertyID = SCOPE_IDENTITY()
|
|
|
|
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES (@SubCategoryID, @PropertyID)
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[DeleteCategory] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[DeleteCategory]
|
|
@CategoryID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @ItemIDs TABLE
|
|
(
|
|
RowNumber INT PRIMARY KEY IDENTITY(1, 1),
|
|
ID INT
|
|
)
|
|
DECLARE @SubCategoryIDs TABLE
|
|
(
|
|
RowNumber INT PRIMARY KEY IDENTITY(1, 1),
|
|
ID INT
|
|
)
|
|
DECLARE @PropertyIDs TABLE
|
|
(
|
|
RowNumber INT PRIMARY KEY IDENTITY(1, 1),
|
|
ID INT
|
|
)
|
|
DECLARE @RowCount INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
-- Gather all Items that are associated with this category.
|
|
INSERT INTO @ItemIDs ([ID])
|
|
SELECT [Item ID]
|
|
FROM [Item]
|
|
WHERE [Item].[Category ID] = @CategoryID
|
|
|
|
SET @RowCount=@@ROWCOUNT
|
|
|
|
DECLARE @CurrentRow INT = 0
|
|
DECLARE @ID INT
|
|
-- Clear all information about the selected items.
|
|
WHILE @CurrentRow < @RowCount
|
|
BEGIN
|
|
SET @CurrentRow = @CurrentRow + 1
|
|
|
|
SELECT @ID = ID FROM @ItemIDs WHERE [RowNumber] = @CurrentRow
|
|
|
|
DELETE FROM [ItemComponent] WHERE [Item ID] = @ID
|
|
|
|
DELETE FROM [PropertyValue] WHERE [Item ID] = @ID
|
|
|
|
DELETE FROM [Item] WHERE [Item ID] = @ID
|
|
END
|
|
-- Gather all child categories that are associated with this category.
|
|
INSERT INTO @SubCategoryIDs ([ID])
|
|
SELECT [Sub Category ID]
|
|
FROM [SubCategory]
|
|
WHERE [Category ID] = @CategoryID
|
|
|
|
SET @RowCount = @@ROWCOUNT
|
|
SET @CurrentRow = 0
|
|
|
|
WHILE @CurrentRow < @RowCount
|
|
BEGIN
|
|
SET @CurrentRow = @CurrentRow + 1
|
|
|
|
SELECT @ID = ID FROM @SubCategoryIDs WHERE [RowNumber] = @CurrentRow
|
|
|
|
INSERT INTO @PropertyIDs ([ID])
|
|
SELECT [Property ID]
|
|
FROM [SubCategoryProperty]
|
|
WHERE [Sub Category ID] = @ID
|
|
|
|
DELETE FROM [SubCategoryProperty] WHERE [Sub Category ID] = @ID
|
|
|
|
DELETE FROM [SubCategory] WHERE [Sub Category ID] = @ID
|
|
END
|
|
-- Clear properties from the sub-categories that were deleted and the category that is being deleted.
|
|
INSERT INTO @PropertyIDs([ID])
|
|
SELECT [Property ID]
|
|
FROM [CategoryProperty]
|
|
WHERE [Category ID] = @CategoryID
|
|
|
|
DELETE FROM [CategoryProperty] WHERE [Category ID] = @CategoryID
|
|
|
|
SELECT @RowCount = COUNT([ID]) FROM @PropertyIDs
|
|
SET @CurrentRow = 0
|
|
|
|
WHILE @CurrentRow < @RowCount
|
|
BEGIN
|
|
SET @CurrentRow = @CurrentRow + 1
|
|
|
|
SELECT @ID = ID FROM @PropertyIDs WHERE [RowNumber] = @CurrentRow
|
|
|
|
DELETE FROM [Property] WHERE [Property ID] = @ID
|
|
END
|
|
-- Finally, delete the category entirely.
|
|
DELETE FROM [Category] WHERE [Category ID] = @CategoryID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[DeleteCategoryProperty] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[DeleteCategoryProperty]
|
|
@CategoryPropertyID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @PropertyID INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
SELECT @PropertyID = [Property ID] FROM [CategoryProperty] WHERE [Category Property ID] = @CategoryPropertyID
|
|
|
|
DELETE FROM [CategoryProperty] WHERE [Category Property ID] = @CategoryPropertyID
|
|
|
|
DELETE FROM [Property] WHERE [Property ID] = @PropertyID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[DeleteSubCategory] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROCEDURE [dbo].[DeleteSubCategory]
|
|
@SubCategoryID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @ItemIDs TABLE
|
|
(
|
|
RowNumber INT PRIMARY KEY IDENTITY(1, 1),
|
|
ID INT
|
|
)
|
|
DECLARE @PropertyIDs TABLE
|
|
(
|
|
RowNumber INT PRIMARY KEY IDENTITY(1, 1),
|
|
ID INT
|
|
)
|
|
DECLARE @RowCount INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
-- Gather all Items that are associated with this sub-category.
|
|
INSERT INTO @ItemIDs ([ID])
|
|
SELECT [Item ID]
|
|
FROM [Item]
|
|
WHERE [Item].[Sub Category ID] = @SubCategoryID
|
|
|
|
SET @RowCount=@@ROWCOUNT
|
|
|
|
DECLARE @CurrentRow INT = 0
|
|
DECLARE @ID INT
|
|
-- Clear all information about the selected items.
|
|
WHILE @CurrentRow < @RowCount
|
|
BEGIN
|
|
SET @CurrentRow = @CurrentRow + 1
|
|
|
|
SELECT @ID = ID FROM @ItemIDs WHERE [RowNumber] = @CurrentRow
|
|
|
|
DELETE FROM [ItemComponent] WHERE [Item ID] = @ID
|
|
|
|
DELETE FROM [PropertyValue] WHERE [Item ID] = @ID
|
|
|
|
DELETE FROM [Item] WHERE [Item ID] = @ID
|
|
END
|
|
-- Clear properties from the sub-categories that were deleted and the category that is being deleted.
|
|
INSERT INTO @PropertyIDs([ID])
|
|
SELECT [Property ID]
|
|
FROM [SubCategoryProperty]
|
|
WHERE [Sub Category ID] = @SubCategoryID
|
|
|
|
DELETE FROM [SubCategoryProperty] WHERE [Sub Category ID] = @SubCategoryID
|
|
|
|
SELECT @RowCount = COUNT([ID]) FROM @PropertyIDs
|
|
SET @CurrentRow = 0
|
|
|
|
WHILE @CurrentRow < @RowCount
|
|
BEGIN
|
|
SET @CurrentRow = @CurrentRow + 1
|
|
|
|
SELECT @ID = ID FROM @PropertyIDs WHERE [RowNumber] = @CurrentRow
|
|
|
|
DELETE FROM [Property] WHERE [Property ID] = @ID
|
|
END
|
|
-- Finally, delete the sub-category entirely.
|
|
DELETE FROM [SubCategory] WHERE [Sub Category ID] = @SubCategoryID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[DeleteSubCategoryProperty] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[DeleteSubCategoryProperty]
|
|
@SubCategoryPropertyID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @PropertyID INT
|
|
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
SELECT @PropertyID = [Property ID] FROM [SubCategoryProperty] WHERE [Sub Category Property ID] = @SubCategoryPropertyID
|
|
|
|
DELETE FROM [SubCategoryProperty] WHERE [Sub Category Property ID] = @SubCategoryPropertyID
|
|
|
|
DELETE FROM [Property] WHERE [Property ID] = @PropertyID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[UpdateProperty] Script Date: 4/13/2021 7:21:17 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[UpdateProperty]
|
|
@PropertyID INT,
|
|
@PropertyName NVARCHAR(128),
|
|
@PropertyTypeID INT
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT, XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
UPDATE [Property] SET [Name] = @PropertyName, [Property Type ID] = @PropertyTypeID WHERE [Property ID] = @PropertyID
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRANSACTION
|
|
END;
|
|
|
|
THROW;
|
|
|
|
END CATCH;
|
|
|
|
SET NOCOUNT, XACT_ABORT OFF;
|
|
END
|
|
GO |