Click here to Skip to main content
15,867,832 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
 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;

SQL
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

SQL
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.
Posted
Updated 6-Jan-14 5:57am
v2
Comments
dbrenth 6-Jan-14 13:57pm    
You can pepper your procedure with select statements so it will give you feedback while it runs. This would be most helpful. We can't see what is in the Student table so it is hard to say.

1 solution

This is a bit of a disaster. I NEVER use cursors, I literally never need to ,and they are messy and slow.

What are you after beyond finding the records that have the values you pass in ? Because

select * from TB_Room_Allocation_SMS where Sess = @sess and DateofCrs = @date

will work just fine with the params you wanted to pass in. If you want something more complex, you need to explain the logic, and provide enough data to cover all the possibilities, so we can should you the proper way to do it.
 
Share this answer
 

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