SQL SERVER产生顺序号存储过程

水电工888

　　首先建张数据表
　　
USE [EBILL_HRBANK]GO/****** 对象:  Table [dbo].[TEMPVOUCHERNO]    脚本日期: 09/06/2011 12:39:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TEMPVOUCHERNO]([my_id] [varchar](7) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF　　
然后创建存储过程
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER PROCEDURE [dbo].[GET_VOUCHERNO]@NEW_ID VARCHAR(7) OUTPUTAS BEGIN--取出最大IDSET @NEW_ID=NULLSELECT TOP 1 @NEW_ID=my_id from TEMPVOUCHERNO ORDER BY my_id DESC--如果未取出来IF @NEW_ID IS NULLSET @NEW_ID='0000001'ELSE  BEGINDECLARE @NUM VARCHAR(7)--取出编号+1SET @NUM=CONVERT(VARCHAR,(CONVERT(INT,RIGHT(@NEW_ID,7))+1))SET @NUM=REPLICATE('0',7-LEN(@NUM))+@NUMSET @NEW_ID=@NUMEND insert into TEMPVOUCHERNO (my_id) values(@NEW_ID)END　　
难点就是存储过程里面的逻辑