SQL生成唯一ID号

USE [test]
GO

/****** Object:  Table [dbo].[IdentityId]    Script Date: 11/08/2011 17:10:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE 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]

GO

SET ANSI_PADDING OFF
GO

USE [test] GO /****** Object: StoredProcedure [dbo].[SP_GetIdentityID] Script Date: 11/08/2011 17:10:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GetIdentityID] @TableName VARCHAR(500), @ColumnNames VARCHAR(100), @NowDateTime DATETIME AS BEGIN SET NOCOUNT ON; DECLARE @minnum BIGINT DECLARE @maxnum BIGINT DECLARE @NextId BIGINT DECLARE @time DATETIME SELECT @NextId=ii.NextID,@time=NowDateTime,@minnum=minnum,@maxnum=maxnum FROM IdentityID ii WHERE ii.TableName=@TableName AND ii.ColumnNames=@ColumnNames DECLARE @IdentityID BIGINT SET @IdentityID=@minnum DECLARE @NextTime DATETIME SET @NextTime=DATEADD(DAY,1,@time) ---数据溢出 IF @NextId>@maxnum BEGIN UPDATE [dbo].[IdentityId] SET [NextID] =@IdentityID ,[NowDateTime] =@NextTime WHERE TableName=@TableName AND ColumnNames=@ColumnNames END --已到达第二天 IF DATEDIFF(day,@time,@NowDateTime)>0 BEGIN UPDATE [dbo].[IdentityId] SET [NextID] =@IdentityID ,[NowDateTime] =@NowDateTime WHERE TableName=@TableName AND ColumnNames=@ColumnNames END BEGIN TRAN UPDATE IdentityID SET NextID=NextID+1 WHERE TableName=@TableName AND 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 @IdentityID SELECT * FROM IdentityID ii WHERE ii.TableName=@TableName AND ii.ColumnNames=@ColumnNames END

USE [test]
GO

EXEC	[dbo].[SP_GetIdentityID]
		@TableName = N'aa',
		@ColumnNames = N'orderid',
		@NowDateTime=N'2011-11-08 12:56:58'
GO

EXEC	[dbo].[SP_GetIdentityID]
		@TableName = N'bb',
		@ColumnNames = N'orderid',
		@NowDateTime=N'2011-11-08 12:56:58'
GO

SELECT * FROM IdentityId ii