求教:sql 事务不回滚 不出错
j119 2011-09-19 09:58:14 存储过程中 将输入的字符串u_Data解析,保存到t_data中,表t_data中,d_headid,d_time是外键,
测试时,如果输入的字符串a中对应d_headid,d_item是错误的,事务不回滚?不进入catch里
是什么原因?
代码
create PROCEDURE [dbo].aaaa
@u_Data as nvarchar(max),
@createtime as datetime,
AS
BEGIN
declare @L int
declare @S int
declare @data nvarchar(100)
declare @i int
declare @j int
declare @upid int
declare @HeadLength int
declare @KindLength int
declare @DataLength int
SET XACT_ABORT ON
set nocount off
begin try
begin tran istart
INSERT INTO [t_up]( ...)
VALUES(.....)
set @upid = @@identity
set @HeadLength = dbo.Get_StrArrayLength(@u_tHead,null)
set @KindLength = dbo.Get_StrArrayLength(@u_tKind,null)
set @DataLength = dbo.Get_StrArrayLength(@u_Data,null)
set @i = 1
set @j = 1
SET @L = 0
SET @S = CHARINDEX(@SPLITCHAR, @u_Data, @L)
while @L<@S
begin
IF @S = 0 SET @S = LEN(@u_Data) + 1
SET @data = SUBSTRING(@u_Data, @L, @S - @L) -- 取值
SET @L = @S + 1
SET @S = CHARINDEX(@SPLITCHAR, @u_Data, @L)
if (case when isnumeric(@data)=1 then cast(@data as numeric(18,2)) else 0 end)=0
begin
set @j = @j +1
if @j > @HeadLength
begin
set @j = 1
set @i = @i + 1
end
CONTINUE
end
INSERT INTO [t_data]
([u_upid] ,[d_headid],[d_item],[d_data])
VALUES(@upid,dbo.Get_StrArray(@u_tHead,null,@j),dbo.Get_StrArray(@u_tKind,null,@i)
,case when isnumeric(@data)=1 then cast(@data as numeric(18,2)) else 0 end)
set @j = @j +1
if @j > @HeadLength
begin
set @j = 1
set @i = @i + 1
end
end
commit tran
set @retState = 0
end try
begin catch
ROLLBACK TRAN
set @retState = -1
end catch
return @retState
END