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 MSgdelivered from Shortcode_Course_SMS where Msgdelivered > @Fromdate and Msgdelivered < @Todate
open shorts
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
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.