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