My stored procedure as follows
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[OH_Course_Elg] @Studid varchar(50)
as
begin
declare @Coursename varchar(50),
@code varchar(50),
@certificate varchar(50),
@issueauth varchar(50),
@issuedate varchar(15)
create table #Temptable (Coursename varchar(10),code varchar(10),certificate varchar(10),issuseauth varchar(10),issuedate varchar(10))
begin tran
declare coursename cursor for
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else cbm.cmn_minor_code end
select distinct cbm.cmn_minor_code
from course_registration cr, batch_course_registration bcr, co_batch_master cbm, bill_file bill, receipt_file rcpt where bcr.cr_bill_no
= cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cbm.cbm_batch_start_dt >; getdate()-1 and cr.stud_id = @Studid
union all
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else cbm.cmn_minor_code end
from course_registration cr, batch_course_registration bcr, co_batch_master cbm, bill_file2 bill, receipt_file2 rcpt where bcr.cr_bill_no
= cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cbm.cbm_batch_start_dt > getdate()-1
and cr.stud_id = @Studid
open coursename
fetch next from coursename into @Coursename
while @@Fetch_status = 0
begin
begin tran
declare crselg cursor for
select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date,
b.Courseelg from student_professional a,tb_courseelg_settings b where a.pm_prof_code= b.courseelg and b.coursename = 'LGTF' and a.stud_id = @studid order by Issue_Date desc
open crselg
fetch next from crselg into @code,@certificate,@issueauth,@issuedate
while @@Fetch_status = 0
begin
insert into #Temptable values(@Coursename,@code,@certificate,@issueauth,@issuedate)
fetch next from crselg into @code,@certificate,@issueauth,@issuedate
end
close crselg
deallocate crselg
commit tran
fetch next from coursename into @Coursename
end
close coursename
deallocate coursename
commit tran
select * from #Temptable
end
When i run the above stored procedure shows error as follows
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The above error shows in below line as follows
while @@Fetch_status = 0
please help me from my store procedure what is the mistake i made.
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[OH_Course_Elg] @Studid varchar(50)
as
begin
declare @Coursename varchar(50),
@code varchar(50),
@certificate varchar(50),
@issueauth varchar(50),
@issuedate varchar(15)
create table #Temptable (Coursename varchar(10),code varchar(10),certificate varchar(10),issuseauth varchar(10),issuedate varchar(10))
begin tran
declare coursename cursor for
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else cbm.cmn_minor_code end
select distinct cbm.cmn_minor_code
from course_registration cr, batch_course_registration bcr, co_batch_master cbm, bill_file bill, receipt_file rcpt where bcr.cr_bill_no
= cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cbm.cbm_batch_start_dt >; getdate()-1 and cr.stud_id = @Studid
union all
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else cbm.cmn_minor_code end
from course_registration cr, batch_course_registration bcr, co_batch_master cbm, bill_file2 bill, receipt_file2 rcpt where bcr.cr_bill_no
= cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cbm.cbm_batch_start_dt > getdate()-1
and cr.stud_id = @Studid
open coursename
fetch next from coursename into @Coursename
while @@Fetch_status = 0
begin
begin tran
declare crselg cursor for
select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date,
b.Courseelg from student_professional a,tb_courseelg_settings b where a.pm_prof_code= b.courseelg and b.coursename = 'LGTF' and a.stud_id = @studid order by Issue_Date desc
open crselg
fetch next from crselg into @code,@certificate,@issueauth,@issuedate
while @@Fetch_status = 0
begin
insert into #Temptable values(@Coursename,@code,@certificate,@issueauth,@issuedate)
fetch next from crselg into @code,@certificate,@issueauth,@issuedate
end
close crselg
deallocate crselg
commit tran
fetch next from coursename into @Coursename
end
close coursename
deallocate coursename
commit tran
select * from #Temptable
end
When i run the above stored procedure shows error as follows
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The above error shows in below line as follows
while @@Fetch_status = 0
please help me from my store procedure what is the mistake i made.
Suraj SahooPosted Mar 10, 2015, 4:30 AM
Please check below:
insert into #Temptable values(@Coursename,@code,@certificate,@issueauth,@issuedate)
fetch next from crselg into @code,@certificate,@issueauth,@issuedate
You are fetching four records but trying to insert into the table five records. Thus the column count mismatch is causing the error probably.
I hope this works.
Accept if helps anyway.
Thanks