我的商店程序代码无效

本文介绍了我的商店程序代码无效的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我的表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表中没有任何房间。 选择 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 @ Mob_num <> ' NULL' BEGIN IF （ @ Mob_num <> ' 9380244904'） BEGIN INSERT INTO #TempTable VALUES （ @ stud_name ， @ Mob_num ， @ Course ， @ Batch_id ， @ RoomNo ， @ Sess ， @ 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 followsSess Bthid Minor_code Roomno DateOfCrs ActiveAM B11756 AFF 11 2013-05-17 AAM B11521 MFA 12 2013-05-17 AAM 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_SMSwhere 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 11for that i written the store procedure as follows;ALTER PROCEDURE [dbo].[Daily_SMS_Students]@session VARCHAR (2), @date DATETIMEASBEGIN 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;ENDWhen i execute the store procedure as followsexec Daily_SMS_Students 'AM','2013-05-18';stud_name Mob_num Course Batch_id RoomNo SessOutput is not coming. it shows no records in the output.when i execute the above store procedure i want output as followsstud_name Mob_num Course Batch_id RoomNo Sess Ram 984057805 AFF B11756 11 AMwhat 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 ? Becauseselect * from TB_Room_Allocation_SMS where Sess = @sess and DateofCrs = @datewill 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. 这篇关于我的商店程序代码无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！