表创建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
---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