表建立ID

/*
---Description: Generate r_id  for reply table
---Author  : zhongcanjun
---Date : 2007-06-13
*/
CREATE PROCEDURE [dbo].[sp_get_code_reply]
 @reply_id CHAR(9) OUTPUT
AS
/*
 Declaraton of Variables Block
*/
DECLARE
 @v_last_cd CHAR(7),
 @v_prefix CHAR(2),
 @v_record INTEGER
/*
 Main Block
*/
 SELECT @v_record = COUNT(*)
 FROM  code_reply
 IF ( @v_record <= 0 ) BEGIN
   INSERT INTO code_reply ( last_cd, last_upd_date )
   VALUES ( 1, CONVERT(VARCHAR,CURRENT_TIMESTAMP,121) )
   SELECT  @v_last_cd = '0000001'
   END
 ELSE BEGIN
   SELECT @v_last_cd = last_cd
   FROM  code_reply
   IF ( @v_last_cd = '9999999' ) BEGIN
     UPDATE code_reply
     SET  last_cd = 1,
      last_upd_date = CONVERT(VARCHAR,CURRENT_TIMESTAMP,121)
   END
   ELSE  BEGIN
     UPDATE code_reply
     SET  last_cd = last_cd + 1,
      last_upd_date = CONVERT(VARCHAR,CURRENT_TIMESTAMP,121)
   END
   SELECT @v_last_cd = last_cd
   FROM  code_reply
   WHILE LEN( @v_last_cd ) <= 6 BEGIN
     SELECT @v_last_cd = '0' + @v_last_cd
   END
 END
 SELECT @v_prefix = SUBSTRING( CONVERT( CHAR(8), GETDATE(), 11 ), 1, 2 )
 
/*
 Return value
*/
 SELECT  @reply_id = @v_prefix + @v_last_cd
GO