当我运行存储过程显示错误如下

本文介绍了当我运行存储过程显示错误如下的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER proc [dbo]。[Presea_RefresherSMS] @ Fromdate varchar （ 50 ）， @ Todate varchar （ 50 ） as begin 声明 @ Course varchar （ 20 ）， @ NoofSt udents varchar （ 20 ）， @Rowcount int ， @ coursedate varchar （ 20 ）， @ batchid varchar （ 20 ） create table #TempTable（course varchar （ 10 ），Noofstudents varchar （ 10 ）） 开始 tran 声明课程光标 选择 cmn_minor_code 为 Course_Name， convert （ char ，cbm_batch_start_dt， 106 ） as Course_date，cbm_batch_id Batch_ID 来自 co_batch_master 其中 cbm_active<> ' D' 和 （（cbm_batch_start_dt @ Fromdate 和 @Todate ）或（cbm_batch_end_dt @ Fromdate 和 @ Todate ）） 打开课程 fetch next 从课程到 @ Course ， @ coursedate ， @ batchid while @@ Fetch_status = 0 开始 开始 tran 声明 studentcount cursor 选择 count（*）来自 batch_course_registration a，course_registration b 其中 b.cr_bill_no = a.cr_bill_no 和 a.bcr_batch_id = ' B8753' 和 b.cr_active = ' A' open studentcount fetch next 来自 studentcount into @ Rowcount while @@ Fetch_status = 0 开始 insert into #TempTable values （ @ Course ， @ coursedate ， @ batchid ） fetch next 从 studentcount 进入 @ Rowcount end close studentcount deallocate studentcount commit tran fetch next course 进入 @ Course ， @ Rowcount ， @ coursedate ， @ batchid end close course Deallocate course commit tran 选择 * 来自 #TempTable 结束 当我执行上述商店程序时显示错误如下 exec [Presea_RefresherSMS] ' 20140605'，' 20140805'。 列名或提供的值数没有匹配表定义。 EXECUTE之后的事务计数表示缺少COMMIT或ROLLBACK TRANSACTION语句。以前的数量= 28，当前数量= 30 请帮助我。从上面的商店程序我犯了什么错误？ 问候， Narasiman P. 解决方案 似乎从studentcount获取下一个@Rowcount 忽略先前创建的列b： 声明 studentcount cursor 选择 count（*）来自 batch_course_registration a，course_registration b 比较两行 fetch next 从课程到 @ Course ， @ coursedate ， @ batchid a nd fetch next 来自 course 进入 @ Course ， @ Rowcount ， @coursedate ， @ batchid 并将它们与光标的声明匹配 声明课程光标 for 选择 cmn_minor_code as Course_Name， convert （ char ，cbm_batch_start_dt， 106 ） as Course_date，cbm_batch_id as Batch_ID 您已包含 @Rowcount，在第二个 fetch 。删除它，错误将被解决。 @Rowcount 正由设置从studentcount到@Rowcount set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER proc [dbo].[Presea_RefresherSMS] @Fromdate varchar(50), @Todate varchar(50)asbegindeclare @Course varchar(20), @NoofStudents varchar(20), @Rowcount int, @coursedate varchar(20), @batchid varchar(20)create table #TempTable (course varchar(10), Noofstudents varchar(10))begin trandeclare course Cursor for select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_ID from co_batch_master where cbm_active <> 'D' and ((cbm_batch_start_dt between @Fromdate and @Todate) or (cbm_batch_end_dt between @Fromdate and @Todate))open coursefetch next from course into @Course,@coursedate,@batchidwhile @@Fetch_status = 0 begin begin tran declare studentcount cursor for select count(*) from batch_course_registration a,course_registration b where b.cr_bill_no = a.cr_bill_no and a.bcr_batch_id = 'B8753' and b.cr_active = 'A' open studentcount fetch next from studentcount into @Rowcount while @@Fetch_status = 0 begin insert into #TempTable values(@Course,@coursedate,@batchid) fetch next from studentcount into @Rowcount end close studentcount deallocate studentcount commit tran fetch next from course into @Course,@Rowcount,@coursedate,@batchid end close course Deallocate course commit transelect * from #TempTableend When i execute the above store procedure shows error as follows exec [Presea_RefresherSMS] '20140605','20140805'. Column name or number of supplied values does not match table definition. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 28, current count = 30 Please help me.From above store procedure what is the mistake i made? Regards, Narasiman P. 解决方案 It seems that fetch next from studentcount into @Rowcount ignores column b previously created:declare studentcount cursor for select count(*) from batch_course_registration a,course_registration bCompare the two lines fetch next from course into @Course,@coursedate,@batchid and fetch next from course into @Course,@Rowcount,@coursedate,@batchidand match them against the declaration of the cursor declare course Cursor for select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_IDYou have included @Rowcount, in the second fetch. Remove that and the error will be resolved.@Rowcount is being set by fetch next from studentcount into @Rowcount 这篇关于当我运行存储过程显示错误如下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！