USE [EBILL_HRBANK] GO /****** 对象: Table [dbo].[TEMPVOUCHERNO] 脚本日期: 09/06/2011 12:39:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TEMPVOUCHERNO]( [my_id] [varchar](7) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
然后创建存储过程
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[GET_VOUCHERNO] @NEW_ID VARCHAR(7) OUTPUT AS BEGIN --取出最大ID SET @NEW_ID=NULL SELECT TOP 1 @NEW_ID=my_id from TEMPVOUCHERNO ORDER BY my_id DESC --如果未取出来 IF @NEW_ID IS NULL SET @NEW_ID='0000001' ELSE BEGIN DECLARE @NUM VARCHAR(7) --取出编号+1 SET @NUM=CONVERT(VARCHAR,(CONVERT(INT,RIGHT(@NEW_ID,7))+1)) SET @NUM=REPLICATE('0',7-LEN(@NUM))+@NUM SET @NEW_ID=@NUM END insert into TEMPVOUCHERNO (my_id) values(@NEW_ID) END