USE [test]GO/****** Object: Table [dbo].[IdentityId] Script Date: 11/08/2011 17:10:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[IdentityId]([TableName] [varchar](500) NOT NULL,[ColumnNames] [varchar](100) NOT NULL,[NextID] [bigint] NOT NULL,[NowDateTime] [datetime] NOT NULL,[MinNum] [bigint] NOT NULL,[MaxNum] [bigint] NOT NULL, CONSTRAINT [PK_IdentityId_1] 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]GOSET ANSI_PADDING OFFGO
USE [test]GO/****** Object: StoredProcedure [dbo].[SP_GetIdentityID] Script Date: 11/08/2011 17:10:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[SP_GetIdentityID] @TableName VARCHAR(500),@ColumnNames VARCHAR(100),@NowDateTime DATETIMEAS BEGIN SET NOCOUNT ON; DECLARE @minnum BIGINTDECLARE @maxnum BIGINTDECLARE @NextId BIGINTDECLARE @time DATETIMESELECT @NextId=ii.NextID,@time=NowDateTime,@minnum=minnum,@maxnum=maxnum FROM IdentityID ii WHERE ii.TableName=@TableName AND ii.ColumnNames=@ColumnNamesDECLARE @IdentityID BIGINTSET @IdentityID=@minnumDECLARE @NextTime DATETIMESET @NextTime=DATEADD(DAY,1,@time)---数据溢出IF @NextId>@maxnumBEGINUPDATE [dbo].[IdentityId] SET [NextID] =@IdentityID ,[NowDateTime] =@NextTimeWHERE TableName=@TableName AND ColumnNames=@ColumnNames END--已到达第二天IF DATEDIFF(day,@time,@NowDateTime)>0BEGINUPDATE [dbo].[IdentityId] SET [NextID] =@IdentityID ,[NowDateTime] =@NowDateTimeWHERE TableName=@TableName AND ColumnNames=@ColumnNamesENDBEGIN TRAN UPDATE IdentityID SET NextID=NextID+1 WHERE TableName=@TableNameAND ColumnNames=@ColumnNames --UPDATE IdentityID SET @IdentityID=NextID,NextID=NextID+1 WHERE TableName=@TableName--AND ColumnNames=@ColumnNames -- IF @@rowcount=0 -- BEGIN ----INSERT INTO IdentityID(TableName,NextID)VALUES(@TableName,@IdentityID+1) -- END COMMIT TRAN --SELECT @IdentityIDSELECT * FROM IdentityID ii WHERE ii.TableName=@TableName AND ii.ColumnNames=@ColumnNamesEND
USE [test]GOEXEC[dbo].[SP_GetIdentityID]@TableName = N'aa',@ColumnNames = N'orderid',@NowDateTime=N'2011-11-08 12:56:58'GOEXEC[dbo].[SP_GetIdentityID]@TableName = N'bb',@ColumnNames = N'orderid',@NowDateTime=N'2011-11-08 12:56:58'GOSELECT * FROM IdentityId ii
