在我的存储过程错误 [英] in my stored procedure error

问题描述

我的存储过程如下

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

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



Cursorfetch：在INTO列表中声明的变量数必须与所选列的变量数相匹配。



上面的错误如下所示，如下所示

而@@ Fetch_status = 0



请帮我从我的商店程序中弄出什么错误。



问候，

Narasiman P.

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.

推荐答案

FETCH（Transact-SQL） [ ^ ]：

变量数必须与游标选择中的列数相匹配list。

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

当你使用FETCH NEXT ... INTO时，局部变量的数量，顺序和类型必须多少数量，顺序，类型o f光标声明中的选定列...

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...

这篇关于在我的存储过程错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！