22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE pr_getjyh @in_pzh char(10),@in_gysh char(6) , @rval integer OUTPUT
AS
DECLARE @rq datetime
DECLARE @spbh char(10)
DECLARE @gysh char(6)
DECLARE @ggxh char(40)
DECLARE @weight decimal(5, 2)
DECLARE @Code char(30)
DECLARE @spmc char(30)
DECLARE csr_getjyh CURSOR FOR SELECT rq,spbh,spmc,gysh, ggxh1, weight FROM f_jdtzd where pzh=@in_pzh Order by spbh
select @rval = -1
begin transaction tran_jyhsh
open csr_getjyh
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print '检验号已用完'
select @rval = -10
return
end
if not exists(select 1 from HJJYH where Code=@Code)
begin
Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print @Code + '插入HJJYH表失败'
select @rval = -12
return
end
update Temp_HJJYH Set bz='1' where Code=@Code
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print @Code + '更新Temp_HJJYH已使用标记失败'
select @rval = -12
return
end
end
else
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print '此检验号已使用'+@Code
select @rval = -11
return
end
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end
commit transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
select @rval=0
GO
CREATE PROCEDURE pr_getjyh @in_pzh char(10),@in_gysh char(6) , @rval integer OUTPUT
AS
DECLARE @rq datetime
DECLARE @spbh char(10)
DECLARE @gysh char(6)
DECLARE @ggxh char(40)
DECLARE @weight decimal(5, 2)
DECLARE @Code char(30)
DECLARE @spmc char(30)
begin transaction tran_jyhsh
DECLARE csr_getjyh scroll CURSOR FOR
SELECT rq,spbh,spmc,gysh, ggxh1, weight
FROM f_jdtzd where pzh=@in_pzh
Order by spbh
select @rval = -1
open csr_getjyh
fetch first from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
if not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id)
begin
rollback transaction tran_jyhsh
print '检验号已用完'
select @rval = -10
return
end
if exists(select 1 from HJJYH where Code=@Code)
begin
rollback transaction tran_jyhsh
print '此检验号已使用'+@Code
select @rval = -11
return
end
Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
print @Code + '插入HJJYH表失败'
select @rval = -12
return
end
update Temp_HJJYH Set bz='1' where Code=@Code
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
print @Code + '更新Temp_HJJYH已使用标记失败'
select @rval = -12
return
end
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end
close csr_getjyh
deallocate csr_getjyh
commit transaction tran_jyhsh
select @rval=0
GO
CREATE PROCEDURE pr_getjyh @in_pzh char(10),@in_gysh char(6) , @rval integer OUTPUT
AS
DECLARE @rq datetime
DECLARE @spbh char(10)
DECLARE @gysh char(6)
DECLARE @ggxh char(40)
DECLARE @weight decimal(5, 2)
DECLARE @Code char(30)
DECLARE @spmc char(30)
begin transaction tran_jyhsh
DECLARE csr_getjyh CURSOR FOR
SELECT rq,spbh,spmc,gysh, ggxh1, weight
FROM f_jdtzd where pzh=@in_pzh
Order by spbh
select @rval = -1
open csr_getjyh
fetch first from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
if not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id)
begin
rollback transaction tran_jyhsh
print '检验号已用完'
select @rval = -10
return
end
if exists(select 1 from HJJYH where Code=@Code)
begin
rollback transaction tran_jyhsh
print '此检验号已使用'+@Code
select @rval = -11
return
end
Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
print @Code + '插入HJJYH表失败'
select @rval = -12
return
end
update Temp_HJJYH Set bz='1' where Code=@Code
IF @@ERROR<>0
begin
rollback transaction tran_jyhsh
print @Code + '更新Temp_HJJYH已使用标记失败'
select @rval = -12
return
end
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end
close csr_getjyh
deallocate csr_getjyh
commit transaction tran_jyhsh
select @rval=0
GO
IF(@@TRANCOUNT>0)
ROLLBACK TRAN