Jeffrey's coding note

USE [DBName]
GO
/****** Object:  Table [dbo].[MaxKeyNumber]    Script Date: 01/25/2017 14:07:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaxKeyNumber](
    [TableName] [varchar](20) NOT NULL,
    [MaxKeyNo] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_MaxKeyNumber] PRIMARY KEY CLUSTERED 
(
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'資料表名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MaxKeyNumber', @level2type=N'COLUMN',@level2name=N'TableName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'目前Key值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MaxKeyNumber', @level2type=N'COLUMN',@level2name=N'MaxKeyNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MaxKeyNumber', @level2type=N'COLUMN',@level2name=N'UpdateTime'
GO
INSERT [dbo].[MaxKeyNumber] ([TableName], [MaxKeyNo], [UpdateTime]) VALUES (N'KIND_DATA', 148, CAST(0x0000A70600E796EB AS DateTime))
/****** Object:  StoredProcedure [dbo].[usp_getMaxNo]    Script Date: 01/25/2017 14:07:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_getMaxNo](@TableName varchar(20))
AS
BEGIN
    DECLARE @Max_NO INT
--檢查流水號是否存在
    IF NOT EXISTS ( SELECT * FROM MaxKeyNumber(NOLOCK)
            WHERE TableName = Upper(@TableName))
    BEGIN
        INSERT INTO MaxKeyNumber
        VALUES ( Upper(@TableName), 1, GETDATE() )
    END
    Set NOCOUNT ON
    --取出流水號並更新資料
    Select @Max_NO = MaxKeyNo+1 From MaxKeyNumber Where TableName = Upper(@TableName)
    BEGIN TRAN
        UPDATE MaxKeyNumber
        SET MaxKeyNo = @Max_NO , UpdateTime = GETDATE()
        WHERE TableName = Upper(@TableName)
    COMMIT TRAN
    Select MaxKeyNo From MaxKeyNumber Where TableName = Upper(@TableName)
END
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_getMaxNo]    Script Date: 01/25/2017 14:07:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[ufn_getMaxNo](
    @TableName varchar(20)   --資料表名稱
)
RETURNS INT
AS
BEGIN
    DECLARE @Max_NO INT
--檢查流水號是否存在
    --取出流水號並更新資料
    Select @Max_NO = MaxKeyNo+1 From MaxKeyNumber Where TableName = Upper(@TableName)
    Return @Max_NO
END
GO