TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
narasiman rao
NA
519
768.3k
when i run the stored procedure show error
Mar 10 2015 4:11 AM
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.
Reply
Answers (
1
)
what is Dynamic sql query?
how to blank row in stored procedure