set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Daily_SMS_Students] @session varchar(2), @date datetime
as
begin
declare @stud_name varchar(100),
@Mob_num varchar(15),
@Course varchar(50),
@Batch_id varchar(20),
@RoomNo int,
@Sess varchar(10),
@RoomFloor varchar(15),
@PreviousRoomNo int
create table #TempTable(stud_name varchar(100),Mob_num varchar(15),Course varchar(50),Batch_id varchar(50),
RoomNo varchar(20),Sess varchar(10),RoomFloor varchar(15))
begin tran
declare Rooms cursor for
select bthid,minor_code,RoomNo,Sess from TB_Room_Allocation_SMS where active <> 'D' and Sess = @session and Dateofcrs = @date order by RoomNo
open Rooms
fetch next from Rooms into @Batch_id,@Course,@RoomNo,@Sess
While @@Fetch_status = 0
begin
set @PreviousRoomNo = 0
select @PreviousRoomNo from TB_Room_Allocation_SMS
where active <> 'D' and Sess = @session and bthid = @Batch_id and Dateofcrs < @date order by DateOfCrs
if @RoomNo <> @PreviousRoomNo
begin
declare Studdetails cursor for
select s.stud_name,MobileNo = case rtrim(isnull(s.stud_mobile,''))
when '' then rtrim(s.stud_telephone) else rtrim(s.stud_mobile) end
from course_registration cr,
batch_course_registration bcr, student s where cr.stud_id = s.stud_id and
bcr.cr_bill_no = cr.cr_bill_no and cr.cr_active = 'A'
and s.stud_active <> 'D' and bcr.bcr_batch_id = @Batch_id
open Studdetails
fetch next from Studdetails into @stud_name,@Mob_num
while @@Fetch_status = 0
begin
if (len(ltrim(rtrim(@Mob_num))) > 9) and @Mob_num <> '' and @Mob_num <> 'NULL'
begin
if (@Mob_num <> '9380244904')
begin
insert into #TempTable values(@stud_name,@Mob_num,@Course,@Batch_id,@RoomNo,@Sess,@RoomFloor)
end
end
fetch next from Studdetails into @stud_name,@Mob_num
end
close Studdetails
deallocate Studdetails
end
fetch next from Rooms into @Batch_id,@Course,@RoomNo,@Sess
end
close Rooms
deallocate Rooms
commit tran
select * from #TempTable
end
TB_Room_Allocation_SMS table as follows
Sess bthid Minor_code Roomno Dateofcrs active
AM B11476 PCT 23 2013-05-17 A
AM B11476 PCT 11 2013-05-16 A
When i execute the Store procedure i am checking for previous date any room is changed for that i written the above store procedure.
when i execute the store procedure i want output as follows
AM B11476 PCT 11 2013-05-16 A
what is the problem in my store procedure