我的商店程序代码无效 [英] My store procedure code is not working
本文介绍了我的商店程序代码无效的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的表TB_Room_Allocation_SMS如下
Sess Bthid Minor_code Roomno DateOfCrs活跃
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我写下面的查询来检查2013-05-18下面的日期（DateOfCrs）TB_Room_Allocation_SMS表中没有任何房间。
< pre lang =SQL> 选择 RoomNo 来自 TB_Room_Allocation_SMS
其中有效<> ' D' 和 Sess = ' AM' 和 bthid = B11756' 和 Dateofcrs< ' 2013-05-18' 订单 by DateOfCrs
执行上述查询输出如下
Roomno
11
for i编写商店程序如下;
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 ），
课程 VARCHAR （ 50 ），
Batch_id VARCHAR （ 50 ），
RoomNo VARCHAR （ 20 ），
Sess VARCHAR （ 10 ）
）;
BEGIN TRANSACTION ;
DECLARE 房间 CURSOR
FOR SELECT bthid，
Minor_code，
Roomno，
Sess
FROM TB_Room_Allocation_SMS
WHERE 有效<> ' D'
AND Sess = @ session
AND Dateofcrs = @日期
ORDER BY Roomno;
OPEN 房间;
FETCH NEXT FROM 房间 INTO @ Batch_id ， @ Course ， @ RoomNo ， @ Sess ;
WHILE @@ Fetch_status = 0
BEGIN
SET @PreviousRoomNo = 0 ;
SELECT @ PreviousRoomNo = Roomno
FROM TB_Room_Allocation_SMS
WHERE 有效<> ' 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 来自学生
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 < span class =code-sdkkeyword> @ Mob_num <> ' NULL'
BEGIN
IF （ @ Mob_num <> ' 9380244904'）
BEGIN
INSERT INTO #TempTable
VALUES （ @ stud_name ， @ Mob_num ， @ Course ， @ Batch_id ， @ RoomNo ， @ Sess ，< span class =code-sdkkeyword> @ RoomFloo [R ）;
END
END
FETCH NEXT FROM Studdetails INTO @ stud_name ， @ Mob_num ;
END
CLOSE Studdetails;
DEALLOCATE Studdetails;
END
FETCH NEXT FROM 房间 INTO @ Batch_id ， @ Course ， @ RoomNo ， @ Sess ;
END
关闭房间;
DEALLOCATE 房间;
COMMIT tran
SELECT *
FROM #TempTable;
END
当我按如下方式执行存储过程时
exec Daily_SMS_Students ' AM'，' 2013年5月18日;
stud_name Mob_num课程Batch_id RoomNo Sess
输出未到来。它在输出中没有显示记录。
当我执行上述存储过程时我想要输出如下
stud_name Mob_num课程Batch_id RoomNo Sess
Ram 984057805 AFF B11756 11 AM
我犯了什么错误？
我该怎么办？
问候，
Narasiman P.
解决方案
这有点像灾难。我永远不会使用游标，我根本不需要，而且它们很混乱和缓慢。
除了找到具有传递值的记录之外，你还有什么？因为
从TB_Room_Allocation_SMS中选择*，其中Sess = @sess和DateofCrs = @date
将起作用你希望传入的参数很好。如果你想要更复杂的东西，你需要解释逻辑，并提供足够的数据来涵盖所有的可能性，所以我们可以以正确的方式去做。
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.
解决方案
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.
这篇关于我的商店程序代码无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
