问题客栈我的商店proecdure代码如下 [英] problem inn my store proecdure code as follows
问题描述
我的商店程序代码如下
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo]。[Keyword_Course_SMS](@ Keyword varchar(10))
as
开始
声明@Batchdate varchar(20),
@Coursefees money
create table #Temptable(Keyword varchar(10),coursename varchar(10))
begin tran
声明
选择a.cmn_minor_code,b.Keyword从CO_MINOR_MASTER作为a,Tb_Course_Keyword作为b,其中
a.cmn_minor_code = b.Keyword和b.Active<> 'D';
声明coursedate光标
从CO_BATCH_MASTER中选择top 1 isnull(crm_course_rate,0)cmn_minor_code = @Keyword
和crm_active<>'D'和(datediff(d,crm_eff_start_dt,getdate())> = 0)和
(datediff(d,getdate(),crm_eff_end_dt )> = 0)
open coursedate
从coursedate获取下一个@Batchdate >
而@@ Fetch_status = 0
开始
开始转运
申报费用游标
从co_rate_master中选择top 1 isnull(crm_course_rate,0),其中cmn_minor_code = @Keyword
和crm_active<>'D'和(datediff(d,crm_eff_start_dt,getdate())> = 0)和
(datediff(d,getdate(),crm_eff_end_dt)> = 0)
开放费用
取消费用到@Coursefees
而@@ Fetch_status = 0
开始
begin tran
insert到#Temptable v alues(@ Batchdate,@ Coursefees)
从费用中取出来到@Coursefees
结束
关闭费用
deallocate费用
commit tran
从coursedate获取下一个@Batchdate
end
close coursedate
deallocate coursedate
commit tran
close course
deallocate course
commit tran
select * from #Temptable
end
执行
exec [Keyword_Course_SMS]'AFF'
当我执行上面的商店程序错误如下
程序Keyword_Course_SMS,第50行
光标未打开。
程序Keyword_Course_SMS,第52行
COMMIT TRANSACTION请求没有相应的BEGIN TRANSACTION。< br $>
第50行如下
deallocate费用
第52行如下
从coursedate获取下一个@Ba tchdate
请帮我解决上面代码中的问题。
问候,
Narasiman P
My store procedure code as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Keyword_Course_SMS](@Keyword varchar(10))
as
begin
declare @Batchdate varchar(20),
@Coursefees money
create table #Temptable (Keyword varchar(10),coursename varchar(10))
begin tran
declare course cursor for
select a.cmn_minor_code,b.Keyword from CO_MINOR_MASTER as a,Tb_Course_Keyword as b where
a.cmn_minor_code = b.Keyword and b.Active <> 'D';
declare coursedate cursor for
select top 1 isnull(crm_course_rate,0) from CO_BATCH_MASTERwhere cmn_minor_code = @Keyword
and crm_active <>'D' and (datediff(d, crm_eff_start_dt, getdate()) >= 0) and
(datediff(d, getdate(), crm_eff_end_dt) >= 0)
open coursedate
fetch next from coursedate into @Batchdate
while @@Fetch_status = 0
begin
begin tran
declare fees cursor for
select top 1 isnull(crm_course_rate,0) from co_rate_master where cmn_minor_code = @Keyword
and crm_active <>'D' and (datediff(d, crm_eff_start_dt, getdate()) >= 0) and
(datediff(d, getdate(), crm_eff_end_dt) >= 0)
open fees
fetch next from fees into @Coursefees
while @@Fetch_status = 0
begin
begin tran
insert into #Temptable values(@Batchdate,@Coursefees)
fetch next from fees into @Coursefees
end
close fees
deallocate fees
commit tran
fetch next from coursedate into @Batchdate
end
close coursedate
deallocate coursedate
commit tran
close course
deallocate course
commit tran
select * from #Temptable
end
Execution
exec [Keyword_Course_SMS] 'AFF'
When i execute the above store procedure Error as follows
Procedure Keyword_Course_SMS, Line 50
Cursor is not open.
Procedure Keyword_Course_SMS, Line 52
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Line 50 as follows
deallocate fees
Line 52 as follows
fetch next from coursedate into @Batchdate
please help me what is the problem in my above code.
Regards,
Narasiman P
推荐答案
检查你的课程光标,它是不完整的。您已将其用于声明,但忘记打开并使用它。
Check your 'course cursor' , It is Incomplete. You have used it for declaration but, forgot to open & use it.
这篇关于问题客栈我的商店proecdure代码如下的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!