自動生成一個流程号( 20170506001 )

--存儲過程,自動生成一個工單号日期+3位流水号20170506001
IF OBJECT_ID(\'auto_mocodehand\')>0
   DROP PROC auto_mocodehand
GO
CREATE PROC auto_mocodehand
 --@Result float OUTPUT --要輸出的值
AS   
declare @today varchar(100)
declare @count int
declare @resultcount int
declare @MoCodeHand varchar(100)
set @today=CONVERT(varchar(100), GETDATE(), 112)
--select @today

set @count=(select count(MoCodeHand) from planinfo where SUBSTRING( MoCodeHand , 1 , 8 )=CONVERT(varchar(100), GETDATE(), 112))
set @count=@count+1
set @MoCodeHand=@today+right(\'000000\'+CAST(@count as varchar(100)),3)
--select @MoCodeHand

set @resultcount=(select count(MoCodeHand) from planinfo where  MoCodeHand=@MoCodeHand)
while @resultcount>0
begin
	set @count=@count+1
	set @MoCodeHand=@today+right(\'000000\'+CAST(@count as varchar(100)),3)
	set @resultcount=(select count(MoCodeHand) from planinfo where  MoCodeHand=@MoCodeHand)
end
select @MoCodeHand
GO
--存儲過程結束

EXECUTE auto_mocodehand