DataDict

出自ProgWiki

目錄 1 用途 2 Table定義 3 SetDataDict 4 ListDataDict 5 FindDataDictID 5.1 FindDataDictID_ByDataName 5.2 FindDataDictID_ByDataValue

用途

• 樹狀資料字典（使用ParentID的欄位，往上串接到上一層，當ParentID為0時，即為頂層的資料字典分類項目）

Table定義

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataDict](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[DataName] [NVARCHAR](16) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[DataValue] [NVARCHAR](32) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[Description] [NVARCHAR](64) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[ParentID] [INT] NOT NULL
) ON [PRIMARY]

SetDataDict

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SetDataDict] (
	@ID AS INT
	,@DataName AS NVARCHAR(16)
	,@DataValue AS NVARCHAR(32)
	,@Description AS NVARCHAR(64)
	,@ParentID AS INT
) AS
BEGIN
	DECLARE @nCount AS INT;
	IF (@ID=0)
		BEGIN
			SELECT @nCount=COUNT(*)
				FROM DataDict
				WHERE ParentID=@ParentID
				AND DataName=@DataName;
		END
	ELSE
		BEGIN
			SELECT @nCount=COUNT(*) FROM DataDict WHERE ID=@ID;
		END
 
	IF (@nCount=0)
		BEGIN
			INSERT INTO DataDict
				(DataName, DataValue, Description, ParentID)
			VALUES
				(@DataName, @DataValue, @Description, @ParentID);
		END
	ELSE
		BEGIN
			IF (@ID=0)
				BEGIN
					UPDATE DataDict
						SET DataName = @DataName
							,DataValue = @DataValue
							,Description = @Description
							,ParentID = @ParentID
						WHERE ParentID=@ParentID
						AND DataName=@DataName;
				END
			ELSE
				BEGIN
					UPDATE DataDict
						SET DataName = @DataName
							,DataValue = @DataValue
							,Description = @Description
							,ParentID = @ParentID
						WHERE ID=@ID;
				END
		END
END

ListDataDict

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[ListDataDict]
(	
	@ParentID AS INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT *
		FROM DataDict
		WHERE ParentID=@ParentID
)
GO

FindDataDictID

FindDataDictID_ByDataName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[FindDataDictID_ByDataName]
(
	@ParentID AS INT
	,@DataName AS NVARCHAR(16)
)
RETURNS INT
AS
BEGIN
	DECLARE @ResultVar AS INT;
	SET @ResultVar = 0
 
	SELECT @ResultVar = ID
		FROM DataDict
		WHERE ParentID=@ParentID
		and DataName=@DataName;
 
	RETURN @ResultVar;
END
GO

FindDataDictID_ByDataValue

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[FindDataDictID_ByDataValue]
(
	@ParentID AS INT
	,@DataValue AS NVARCHAR(32)
)
RETURNS INT
AS
BEGIN
	DECLARE @ResultVar AS INT;
	SET @ResultVar = 0
 
	SELECT @ResultVar = ID
		FROM DataDict
		WHERE ParentID=@ParentID
		and DataValue=@DataValue;
 
	RETURN @ResultVar;
END
GO