When I Run The Store Procedure Shows Error As Follows

Question

1.50/5 (3 votes)

See more:

SQL-Server-2012

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[shortcode_coursebookingrpt] (@Fromdate varchar(20),@Todate varchar(20))
as 
begin

declare
   @stud_id varchar(10),
   @Mobileno varchar(15),
   @Messagetext varchar(10),
   @Msgdelivered  varchar(20),
   @Replymsg varchar(200)     

create table #TempTable(stud_id varchar(10),Mobileno varchar(100),Messagetext varchar(20),Msgdelivered  varchar(20),Replymsg varchar(200)) 

begin tran
declare short cursor for 
   select Message,Mobileno,MSgdelivered,Replymsg from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate

open short 
fetch next from short into @Msgdelivered
	While @@Fetch_status = 0
	   begin
		 begin tran
		   declare coursebooked cursor for  
               select s.stud_id,s.stud_mobile,sh.Mobileno,cbm.cmn_minor_code,
               case when cbm.cmn_minor_code = sh.Message then 'Booked' end as Booked_Status,
               case when cbm.cmn_minor_code <> sh.Message then 'Not Booked' end as UnBooked_Status
               from STUDENT s,course_registration cr,co_batch_master cbm,Shortcode_Course_SMS sh,batch_course_registration bcr 
               where cr.stud_id = s.stud_id and substring(sh.mobileno,3,20) = s.stud_mobile and sh.Message = cbm.cmn_minor_code and 
               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_active <> 'D' 
           open coursebooked 
                fetch next from coursebooked into @stud_id,@Mobileno
					while @@Fetch_status = 0
					begin
                        insert into #TempTable values(@stud_id,@Mobileno,@Messagetext,@Msgdelivered,@Replymsg)  
					fetch next from coursebooked into @stud_id,@Mobileno
					end 			
				close coursebooked
				deallocate coursebooked
			commit tran
     fetch next from short into @Msgdelivered
     end	
	close short
	deallocate short
	commit tran
select * from #TempTable
end

When i execute the above store procedure shows error as follows

exec [shortcode_coursebookingrpt] '20140205','20140209'
 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

please help me.what is the problem in my above store procedure.

Regards,
Narasiman P.

Posted 15-Apr-14 1:42am

Member 8054539

Updated 15-Apr-14 2:09am

CHill60

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2014-04-15T02:22:00

sanket saxena is correct.

Specifically look at these lines

SQL

declare short cursor for
   select Message,Mobileno,MSgdelivered,Replymsg 
from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate

open short
fetch next from short into @Msgdelivered

The bits in bold are important.
You have declared that your cursor will be retrieving four items from each row, but when you do the fetch you have only supplied one "holder" (variable) for the results. Four into One does not go!

Either change the cursor declaration to

SQL

declare short cursor for
   select MSgdelivered from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate

OR, provide enough variables when you do the FETCH

SQL

open short
fetch next from short into @Message, @Mobileno, @Msgdelivered, @ReplyMsg

You will need to do something similar with the other cursors too

Sanket Saxena · Answer 2 · 2014-04-15T01:58:00

Solution 1

Seems like your SELECT Columns are not same as INTO columns in the cursor. Hope it helps :)

Posted 15-Apr-14 1:58am

Sanket Saxena

Andrius Leonavicius · Answer 3 · 2014-04-15T02:16:00

Hi,

Your SELECT and FETCH...INTO are not matching.

Try this:

SQL

declare short cursor for 
select Message,Mobileno,MSgdelivered,Replymsg from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate

SQL

declare coursebooked cursor for 
select s.stud_id,s.stud_mobile,sh.Mobileno,cbm.cmn_minor_code,
case when cbm.cmn_minor_code = sh.Message then 'Booked' end as Booked_Status,
case when cbm.cmn_minor_code <> sh.Message then 'Not Booked' end as UnBooked_Status
from STUDENT s,course_registration cr,co_batch_master cbm,Shortcode_Course_SMS sh,batch_course_registration bcr 
where cr.stud_id = s.stud_id and substring(sh.mobileno,3,20) = s.stud_mobile and sh.Message = cbm.cmn_minor_code and 
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_active <> 'D'

Selected columns and fetched into variables must match!

When I Run The Store Procedure Shows Error As Follows

3 solutions

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0