Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 shorts cursor for 
--   select Message,Mobileno,MSgdelivered,Replymsg from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate
   select MSgdelivered from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate
open shorts 
--fetch next from short into @Messagetext,@Mobileno,@Msgdelivered,@Replymsg
fetch next from shorts into @Msgdelivered
	While @@Fetch_status = 0
	   begin
		 begin tran
		   declare coursebookeds cursor for  
               select s.stud_id,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 UnBookedStatu 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 coursebookeds 
                fetch next from coursebookeds into @stud_id,@Mobileno
					while @@Fetch_status = 0
					begin
                        insert into #TempTable values(@stud_id,@Mobileno,@Messagetext,@Msgdelivered,@Replymsg)  
					fetch next from coursebookeds into @stud_id,@Mobileno
					end 			
				close coursebookeds
				deallocate coursebookeds
			commit tran
     fetch next from shorts into @Msgdelivered
     end	
	close shorts
	deallocate shorts
	commit tran
select * from #TempTable
end


When i run the store procedure shows error as follows

SQL
exec [shortcode_coursebookingrpt] '20140205','20140209'



The error shows in below line as follows

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' 



please help me what is the problem in my store procedure

Regards,
Narasiman P.
Posted
Updated 15-Apr-14 3:27am
v2
Comments
Richard C Bishop 15-Apr-14 9:29am    
What does the error message say?
thatraja 16-Apr-14 5:39am    
Glad, you have solved the issue

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