Store Procedure is not working properly

Question

  My store Procedure as follows

 

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