存储过程不更新多行

更改程序dbo.sp_UpdateJobItem @JobID as int ，@ Status as as int AS - ============================== ==================== ============================== ============= SET XACT_ABORT OFF - 允许程序继续后 错误 DECLARE @错误整数 - 本地变量捕获 错误OnHoldAction。 - ============ ====================================== ============ =============================== BEGIN TRANSACTION UPDATE tbl_JobItems SET状态= @status WHERE JobID = @JobID - ============================================ ====== =========================================== - 检查错误 - ==================== ============================== ==================== ======================= SELECT @error = @ERROR 如果@error> 0 BEGIN ROLLBACK交易 结束 否则 BEGIN COMMIT TRANSACTION 结束 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOI''m using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case ''JobID''). Butwhat''s happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO推荐答案> UPDATE tbl_JobItems> UPDATE tbl_JobItems SET Status = @status WHERE JobID = @JobID SET Status = @status WHERE JobID = @JobID 由于WHERE子句仅基于JobID， UPDATE语句将使用该JobID更新每一行。您打算如何确定更新哪一行？b $ b行？这张桌子的关键是什么？ - David Portas SQL Server MVP -As the WHERE clause is based only on JobID this UPDATE statement willupdate every row with that JobID. How do you intend to determine whichrow to update? What is the key of this table?--David PortasSQL Server MVP--我正在使用jobID字段来确定要更新的记录。这是关键所在。 。因此，对于示例中的73412的给定jobid，应该更新具有该id的所有行 。I''m using the jobID field to determine which records to update. That''sthe key. So for a given jobid of 73412 like in the example, all rowswith that id should be updated.你有没有在某处使用过SET ROWCOUNT 1？可以肯定的是，尝试在UPDATE之前将proc ROWCOUNT 0放入proc中。 - David Portas SQL Server MVP -Have you used SET ROWCOUNT 1 somewhere? To be sure, try putting SETROWCOUNT 0 in the proc just before the UPDATE.--David PortasSQL Server MVP-- 这篇关于存储过程不更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！