KeyValue

出自ProgWiki

目錄 1 用途 2 Table定義 3 設定變數值 4 讀取變數值 5 應用範例

用途

• 保留變數值於 Table KeyValue 內。（凡是不為字串的變數值，使用時另行以 convert 進行轉換）

Table定義

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyValue](
	[KeyName] [NVARCHAR](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[KeyValue] [NVARCHAR](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]

設定變數值

• SQL 預存程序

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[setKeyValue] (
	@KeyName AS NVARCHAR(50),
	@KeyValue AS NVARCHAR(50)
) AS
BEGIN
	DECLARE @nCount AS INT;
	SELECT @nCount=COUNT(*) FROM [KeyValue] WHERE [KeyName]=@KeyName;
	IF (@nCount=0)
		BEGIN
			INSERT INTO [KeyValue] (
				[KeyName]
				,[KeyValue]
			) VALUES (
				@KeyName
				,@KeyValue
			);
		END
	ELSE
		BEGIN
			UPDATE [KeyValue] SET 
				[KeyValue]=@KeyValue
			WHERE [KeyName]=@KeyName;
		END
END

讀取變數值

• SQL 預存函數

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getKeyValue] (
	@KeyName AS NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @KeyValue AS NVARCHAR(50);
	SET @KeyValue = '';
	SELECT @KeyValue=KeyValue FROM [KeyValue] WHERE [KeyName]=@KeyName;
 
	RETURN @KeyValue;
END

應用範例

• 自訂的Table快取機制與KeyValue應用