一个存储过程的问题!

08-05-25 yanfeilong

我想实现是自动生成一个加Data时间为编号的列,然后把后边的字段插到表里,大家给看下这个过程有什么问题, 我这边显示的错误是说什么varchar转int是错误!

CREATE PROCEDURE insertTest

@name varchar(20),

@regist varchar(10),

@age int,

@password varchar(20),

@weiyibiaoshi varchar(50)=''

OUTPUT

AS

begin

declare

@num int,

@strNum varchar(6),

@Y varchar(4),

@M varchar(3),

@D varchar(3),

@YMD varchar(8),

@Date smalldatetime

set @Date=getdate()

set @Y=Cast(Year(@Date) as varchar(4))

set @Y=right(@Y,4)

set @M=cast(month(@Date) as varchar(3))

set @M='0'+@M

set @M=right(@M,2)

set @D=cast(day(@Date) as varchar(3))

set @D='0'+@D

set @D=right(@D,2)

set @YMD=@Y+@M+@D

begin transaction

set @num=cast(right(@regist,4) as int)

set @regist=SUBSTRING(@regist,1,8)

if @YMD=@regist

begin

set @num=@num+1

set @strNum=cast(@num as varchar(4))

set @strNum='000'+@strNum

set @strNum=right(@strNum,4)

set @weiyibiaoshi=@YMD+@strNum+''

end

else

begin

set @weiyibiaoshi=@YMD+'0001'

end

insert into test(regist_id,weiyibiaoshi,names,age,passwords) VALUES(@regist,@weiyibiaoshi,@name,@age,@password);

if @@error <> 0

begin

rollback transaction

return 0

end

else

begin

commit transaction

end

end

GO

{call insertTest('google','3','admin')}

有一张test表 字段为:regist_id vachar,主键,weiyibiaoshi varchar,names varchar,age int,passwords varchar

freeren
2008-07-31 11:41

兄弟,有点跑错地方了吧?