Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My stored procedure as follows
SQL
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.

Regards,
Narasiman P.
Posted
Updated 9-Mar-15 21:53pm
v2
Comments
Jitendra Ku. Sahoo 10-Mar-15 4:19am    
Have a look here, column no does not match
WHILE @@Fetch_status = 0
BEGIN
INSERT INTO #Temptable
VALUES (
@Coursename
,@code
,@certificate
,@issueauth
,@issuedate
)

FETCH NEXT
FROM crselg
INTO @code
,@certificate
,@issueauth
,@issuedate
END

1 solution

The number of variables must match the number of columns in the cursor select list.

When you use FETCH NEXT ... INTO, the number, order and type of the local variables must much the number, order, type of the selected columns in the cursor declaration...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900