SQL SERVER产生顺序号存储过程
首先建张数据表USE GO/****** 对象:Table . 脚本日期: 09/06/2011 12:39:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE .( (7) COLLATE Chinese_PRC_CI_AS NULL) ON GOSET ANSI_PADDING OFF
然后创建存储过程
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER PROCEDURE .@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'ELSEBEGINDECLARE @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
难点就是存储过程里面的逻辑
页:
[1]