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没有细化的学习过,请专业人士帮忙查看下我这存储过程有哪些问题,请指出并给予正确的写法,万分感谢!
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没有细化的学习过,请专业人士帮忙查看下我这存储过程有哪些问题,请指出并给予正确的写法,万分感谢!
解决方案 »
- 这样一个正则表达式要怎么写?
- sqlserver2000 数据库阻塞
- 在sql server中如何知道某张表有多大,占多少空间?
- 想实现这个,用SQL怎么写
- 我的事务处理有问题,请帮忙看看。
- 急!求助!公司要抓几副SQLServer2000中的图片,谁有时间呀,帮忙看看!???
- You restore the backup copies of the corrupted data files, which
- 急急急!如何在DEVELOP2000(V6.0)前台中实现用户输入密码,文本框显示*号?请各位大虾赐教!
- 给100分求做一触发器,在线等呀.......快来救命呀~~~~~~
- 急!小弟想做个数据厍管理系统不知怎么下手,希望大家能进来聊聊,介绍几本好书源码也好而且还想加上网络功能.
- 裸求解决方案---关于分布式事务错误:OLE DB 访问接口 "SQLNCLI10" 返回了消息 "没有活动事务。"
- 关于ROWGUIDCOL属性
1.先判断Temp_HJJYH表中的检验号是否用完(已使用过的bz=1),已用完则回滚
2.在插入HJJYH表前先判断要插入的检验号在HJJYH表中是否已存在,存在则回滚
3.HJJYH表中记录插入成功后更新Temp_HJJYH表中已用的检验号bz=1,更新出错则回滚
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print '此检验号已使用'+@Code
select @rval = -11
return
end
代码哪里有错误呢?请高手指点
IF @@ERROR<>0
这样判断是不成立的,也应该用
IF not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id
) 判断才对
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
if not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id)
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
print '检验号已用完'
select @rval = -10
return
end if exists(select 1 from HJJYH where Code=@Code)
begin
rollback transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
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
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
endfetch 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
ROLLBACK TRAN
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_jyhshDECLARE csr_getjyh CURSOR FOR
SELECT rq,spbh,spmc,gysh, ggxh1, weight
FROM f_jdtzd where pzh=@in_pzh
Order by spbhselect @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
endfetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
endclose csr_getjyh
deallocate csr_getjyhcommit transaction tran_jyhshselect @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)
DECLARE @result int
DECLARE csr_getjyh CURSOR FOR SELECT rq,spbh,spmc,gysh, ggxh1, weight FROM f_jdtzd where pzh=@in_pzh Order by spbh
begin transaction
select @rval = -1
open csr_getjyh
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
IF not exists(Select 1 From Temp_HJJYH Where bz='0' )
begin
rollback transaction
close csr_getjyh
deallocate csr_getjyh
print '检验号已用完'
select @rval = -10
return
end Select TOP 1 @Code = Code From Temp_HJJYH Where bz='0' order by id
IF @@ERROR<>0
begin
rollback transaction
close csr_getjyh
deallocate csr_getjyh
print @Code + '插入HJJYH表失败'
select @rval = -11
return
end
IF exists(select 1 from HJJYH where LTRIM(RTRIM(Code))=LTRIM(RTRIM(@Code)))
begin
rollback transaction
close csr_getjyh
deallocate csr_getjyh
print '此检验号已使用'+@Code
select @rval = -12
return
end
Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
IF @@ERROR<>0
begin
rollback transaction
close csr_getjyh
deallocate csr_getjyh
print @Code + '插入HJJYH表失败'
select @rval = -13
return
end
update Temp_HJJYH Set bz='1' where Code=@Code
IF @@ERROR<>0
begin
rollback transaction
close csr_getjyh
deallocate csr_getjyh
print @Code + '更新Temp_HJJYH已使用标记失败'
select @rval = -14
return
end
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end
commit transaction
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_jyhshDECLARE csr_getjyh scroll CURSOR FOR
SELECT rq,spbh,spmc,gysh, ggxh1, weight
FROM f_jdtzd where pzh=@in_pzh
Order by spbhselect @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
endfetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
endclose csr_getjyh
deallocate csr_getjyhcommit transaction tran_jyhshselect @rval=0
GO