--创建测试用户的表与数据
CREATE TABLE [dbo].[t1](
[id] [int] IDENTITY(1,1) NOT NULL,
[userId] [varchar](20) COLLATEChinese_PRC_CI_AS NULL,
[money] [varchar](50) COLLATEChinese_PRC_CI_AS NULL,
[state] [int] NULL
)
CREATE TABLE [dbo].[t2](
[id] [int] IDENTITY(1,1) NOT NULL,
[userId] [varchar](20) COLLATEChinese_PRC_CI_AS NULL,
[money] [decimal](10, 2) NULL,
[date] [datetime] NULL DEFAULT (getdate())
)
insert into t1 select 'jsm','1020','0' union all select 'dj','200','0' union all select 'jq','300','0'
declare @userid varchar(128),@MONEY varchar(50),@id int
declare crs_user cursor for select userid,[money],id from t1 where state=0
open crs_user
fetch next from crs_user into @userid,@MONEY,@id
while(@@fetch_status=0)
begin
print @userid
if(ISNUMERIC(@MONEY)=1)--ISNUMERIC方法判断@MONEY是否为有效的整数、浮点数、money 或decimal 类型。返回值为1 时,指示可将expression 至少转换为上述数值类型中的一种,否则返回 0
Begin
begin tran --开始执行事务
update t1 set state=1 where id=@id
insert into t2(userID,[money]) VALUES(@userid,@MONEY)
if(@@error>0)
begin
rollback tran
print 'rollback'
end
else
begin
commit tran
print 'commit'
end
end
fetch next from crs_user into@userid,@MONEY,@id
end
close crs_user
deallocate crs_user