存储过程生成唯一ID [英] Store Procedure To Generate Unique ID

问题描述

先生/女士

我在Asp.Net上使用My Sql Server 2008有一个Web应用程序。



问题：我想生成当前年当前月份的唯一ID，以0000-9999数字。示例201310260001

它应该是自动增量..任何解决方案都将受到欢迎，这将通过存储过程完成。当用户提交表单时，该程序将检查最后一个ID并增加一个201310260002







谢谢提前

解决方案

您也可以使用唯一标识符，但请注意，这不是优化！因为它在全世界创造了一个独特的价值，所以它很大！

  CREATE   TABLE  myTable（ColumnA  uniqueidentifier   DEFAULT  NEWSEQUENTIALID（））

和插入：

  insert   into  myTable（ColumnA） values （NEWID（））

了解更多信息请查看链接：

http://technet.microsoft .com / zh-cg / library / ms189786.aspx [ ^ ]

http：// technet。 microsoft.com/en-sg/library/ms190348.aspx [ ^ ]

我想以下代码将满足您的要求。它将根据日期生成并增加id，并将其存储到名为#MyTable的表中。请检查并测试代码。我测试并发现你的要求没问题

 创建 表 #MyTable（Id  bigint ，名称 varchar （< span class =code-digit> 100 ））; 
  go  
 
 创建  proc  [dbo]。[InsertMaxValue] 
  as  
  begin  
 
 声明  @ IdGenByCurrentDate   bigint  =（选择 cast（年（getdate（））为  varchar ）+ cast（month（getdate（）） as   varchar ）+ + cast（day（getdate（）） as   varchar ））; 
 声明  @ LastId   bigint  =（选择 max（id）来自 #MyTable）; 
 声明  @ NewId   bigint  = cast（ @ IdGenByCurrentDate   as   varchar （  100 ））+ '  0001'; 
 
 如果  @ LastId  是 不  null  
  begin  
 声明  @ LastIdGenByCurrentDate   bigint  = substring（cast（ @ LastId   as   varchar （ 100 ））， 1 ，len（ @IdGenByCurrentDate ））; 
 
 如果  @ IdGenByCurrentDate  =  @LastIdGenByCurrentDate  
  set   @ NewId  =  @LastId  +  1 ; 
  end  
  insert   into  #Mytable（Id，Name） values （ @ NewId ，cast（getdate（） as   varchar （ 111 ）））; 
  end  
 
  exec  InsertMaxValue 
 
 从 #MyTable 

Sir/Madam
I have a Web Application in Asp.Net With My Sql Server 2008 .

Question: I want to Generate Unique Id Like Current Year Current Month Current Date With 0000 -9999 Number. Example 201310260001
it should be autoincrement .. any solution will be appreciate and this will be done with Store Procedure. When user submit form then that procedure will check the last id and increment with one 201310260002



Thanks In Advance

解决方案

Also you can use unique identifier ,but be noted this is not optimize ever! as it creates a unique value in all the world so it is larg!

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID()) 

and for insertion:

insert into myTable(ColumnA) values(NEWID())

for more information check the links:
http://technet.microsoft.com/en-sg/library/ms189786.aspx[^]
http://technet.microsoft.com/en-sg/library/ms190348.aspx[^]

i guess the following code will fulfill your requirement. It will generate and increase id based on date and store it to table named #MyTable. Please check and test the code. I tested and found ok with your requirement

create table #MyTable(Id bigint, Name varchar(100));
go

create proc [dbo].[InsertMaxValue]
as
begin
    
	declare @IdGenByCurrentDate bigint = (select cast(year(getdate()) as varchar) + cast(month(getdate()) as varchar) + + cast(day(getdate()) as varchar));
	declare @LastId bigint = (select max(id) from #MyTable);
	declare @NewId bigint = cast(@IdGenByCurrentDate as varchar(100)) + '0001';

	if @LastId is not null	
	begin
		declare @LastIdGenByCurrentDate bigint = substring(cast(@LastId as varchar(100)), 1, len(@IdGenByCurrentDate));

		if @IdGenByCurrentDate = @LastIdGenByCurrentDate		
			set @NewId = @LastId + 1;			
	end
	insert into #Mytable(Id,Name) values (@NewId, cast(getdate() as varchar(111)));
end

exec InsertMaxValue

select * from #MyTable

这篇关于存储过程生成唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！