Description
Categories and code are used extensively in programming. A category such as “States” could have many associated codes (Florida – FL, Georgia – GA, etc…). Codes typically have a display value (sucha as Florida, Georgia, …) and a return value (such as FL, GA, …). The SQL Script for creating the Category-Code System in the database (written in T-SQL for SQLServer) is below.
Video
Video for this lecture has not been produced yet.
Reference Materials
SQL Script for Category Code Management System Creation
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ccm')
EXEC dbo.sp_grantdbaccess @loginame = N'ccm', @name_in_db = N'ccm'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Codes]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Codes](
[CodeID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[CodeNameText] [varchar](250) NOT NULL,
[DescriptionText] [varchar](250) NULL,
[DeletedBit] [bit] NOT NULL DEFAULT (0),
[LastUpdUserLoginID] [int] NULL,
[LastUpdDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED
(
[CodeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Categories]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryNameText] [varchar](50) NOT NULL,
[DescriptionText] [varchar](250) NULL,
[ParentCategoryID] [int] NULL,
[ParentCodeID] [int] NULL,
[DeletedBit] [bit] NOT NULL DEFAULT (0),
[LastUpdUserLoginID] [int] NULL,
[LastUpdDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetAllCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[spGetAllCategories]
AS
BEGIN
select * from categories
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spInsertCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spInsertCategories]
@CategoryNameText varchar(50),
@DescriptionText varchar(250) = null,
@ParentCategoryID int = null,
@ParentCodeID int = null,
@DeletedBit bit = 0,
@LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Categories(
CategoryNameText,
DescriptionText,
ParentCategoryID,
ParentCodeID,
DeletedBit,
LastUpdUserLoginID,
LastUpdDate)
VALUES(
@CategoryNameText,
@DescriptionText,
@ParentCategoryID,
@ParentCodeID,
@DeletedBit,
@LastUpdUserLoginID,
GetDate())
SELECT MAX(CategoryID) as CategoryID FROM Categories
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUpdateCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateCategories]
/* This assumes the first line is the PK */
@CategoryID int,
@CategoryNameText varchar(50),
@DescriptionText varchar(250) = null,
@ParentCategoryID int = null,
@ParentCodeID int = null,
@DeletedBit bit,
@LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Categories SET
CategoryNameText = @CategoryNameText,
DescriptionText = @DescriptionText,
ParentCategoryID = @ParentCategoryID,
ParentCodeID = @ParentCodeID,
DeletedBit = @DeletedBit,
LastUpdUserLoginID = @LastUpdUserLoginID,
LastUpdDate = GetDate()
WHERE CategoryID = @CategoryID
SELECT * FROM Categories WHERE CategoryID = @CategoryID
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCategories]
@CategoryID int
AS
BEGIN
SELECT * FROM Categories WHERE CategoryID = @CategoryID
END;
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spInsertCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spInsertCodes]
/* If first field is identity - it should be removed
from the passed parameters and the insert code */
@CategoryID int,
@CodeNameText varchar(250),
@DescriptionText varchar(250) = null,
@DeletedBit bit = 0,
@LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Codes(
CategoryID,
CodeNameText,
DescriptionText,
DeletedBit,
LastUpdUserLoginID,
LastUpdDate)
VALUES(
@CategoryID,
@CodeNameText,
@DescriptionText,
@DeletedBit,
@LastUpdUserLoginID,
GetDate())
SELECT MAX(CodeID) FROM Codes
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUpdateCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateCodes]
/* This assumes the first line is the PK */
@CodeID int,
@CategoryID int,
@CodeNameText varchar(250),
@DescriptionText varchar(250) = null,
@DeletedBit bit,
@LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Codes SET
CategoryID = @CategoryID,
CodeNameText = @CodeNameText,
DescriptionText = @DescriptionText,
DeletedBit = @DeletedBit,
LastUpdUserLoginID = @LastUpdUserLoginID,
LastUpdDate = GetDate()
WHERE CodeID = @CodeID
SELECT * FROM Codes WHERE CodeID = @CodeID
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCodes]
@CodeID int
AS
BEGIN
SELECT * FROM Codes WHERE CodeID = @CodeID
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCodesForCategory]
@CategoryID int
AS
BEGIN
SELECT * FROM Codes WHERE CategoryID = @CategoryID
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCodesForCategory]
@CategoryID int
AS
BEGIN
SELECT * FROM Codes WHERE CategoryID = @CategoryID AND DeletedBit = 0
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetAllCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetAllCodesForCategory]
@CategoryID int
AS
BEGIN
SELECT * FROM Codes WHERE CategoryID = @CategoryID
END'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Codes_Categories]') AND type = 'F')
ALTER TABLE [dbo].[Codes] WITH NOCHECK ADD CONSTRAINT [FK_Codes_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[Codes] CHECK CONSTRAINT [FK_Codes_Categories]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Categories_Categories]') AND type = 'F')
ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD CONSTRAINT [FK_Categories_Categories] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Categories]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Categories_Codes1]') AND type = 'F')
ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD CONSTRAINT [FK_Categories_Codes1] FOREIGN KEY([ParentCodeID])
REFERENCES [dbo].[Codes] ([CodeID])
GO
ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Codes1]