My Table TB_Room_Allocation_SMS as follows
Sess Bthid Minor_code Roomno DateOfCrs Active
AM B11756 AFF 11 2013-05-17 A
AM B11521 MFA 12 2013-05-17 A
AM B11756 AFF 13 2013-05-18 A
i am writing below query to check for the 2013-05-18 below Date(DateOfCrs) any room no is there in the TB_Room_Allocation_SMS table.
select RoomNo from TB_Room_Allocation_SMS
where active <> 'D' and Sess = 'AM' and bthid = 'B11756' and Dateofcrs < '2013-05-18' order by DateOfCrs
For executing a above query output as follows
Roomno
11
for that i written the store procedure as follows;
ALTER PROCEDURE [dbo].[Daily_SMS_Students]
@session VARCHAR (2), @date DATETIME
AS
BEGIN
DECLARE @stud_name AS VARCHAR (100),
@Mob_num AS VARCHAR (15),
@Course AS VARCHAR (50),
@Batch_id AS VARCHAR (20),
@RoomNo AS INT,
@Sess AS VARCHAR (10),
@PreviousRoomNo AS 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)
);
BEGIN TRANSACTION;
DECLARE Room 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 Room;
FETCH NEXT FROM Room INTO @Batch_id, @Course, @RoomNo, @Sess;
WHILE @@Fetch_status = 0
BEGIN
SET @PreviousRoomNo = 0;
SELECT @PreviousRoomNo = Roomno
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 stud_name,stud_mobile from Student
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 Room INTO @Batch_id, @Course, @RoomNo, @Sess;
END
CLOSE Room;
DEALLOCATE Room;
COMMIT tran
SELECT *
FROM #TempTable;
END
When i execute the store procedure as follows
exec Daily_SMS_Students 'AM','2013-05-18';
stud_name Mob_num Course Batch_id RoomNo Sess
Output is not coming. it shows no records in the output.
when i execute the above store procedure i want output as follows
stud_name Mob_num Course Batch_id RoomNo Sess
Ram 984057805 AFF B11756 11 AM
what is the mistake i made?
how can i do?
Regards,
Narasiman P.