存储过程不更新多行

本文介绍了存储过程不更新多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用存储的进程，该进程应根据提供的键值更新 表中的多个行（在本例中为JobID）。但是 当我从程序中调用proc时，发生了什么，只有 一行得到更新。 所以 当我从查询分析器调用proc时，所有行都会更新。 当我从程序中调用proc时，只有一行得到更新 关于为什么会发生这种情况的任何想法？ 工作ID描述价格状态 --- ------------------------------------------- 73412文件：Item 3 .00 0 73412文件：Item 5 .00 0 73412文件：Item 2 .00 0 73412文件：项目4 .00 0 73412文件：项目1.00 0 ^^^^只有一条记录得到更新，所以桌子最终是... 工作ID描述价格状态 ---------------------- ------------------------ 73412文件：第3项.00 4 73412文件：项目5 .00 0 73412文件：项目2 .00 0 73412文件：项目4 .00 0 73412文件：项目1 .00 0 Public Sub UpdateAllItems（）As Boolean Dim objCnn As ADODB.Connection Dim objCmd As ADODB.Command 设置objCnn =新ADODB.Connection 使用objCnn .ConnectionString = cnConn .CursorLocation = adUseClient 。打开 结束 设置objCmd =新ADODB.Command 设置objCmd.ActiveConnection = objCnn 使用objCmd .CommandText =" sp_UpdateJobItem" .CommandType = adCmdStoredProc .Parameters.Append。 CreateParameter（" @ Status"，adInteger， adParamInput，4，Me.Status） .Parameters.Append .CreateParameter（" @ JobID"，adInteger， adParamInput，4，Me.iJobID） 。执行 结束 Se t objCnn = Nothing 设置objCmd = Nothing End Sub ---------------- ------------------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO 更改程序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 GO

I''m using a stored proceedure which should update a number of rows in a table depending on a key value supplied (in this case ''JobID''). But what''s happening is when I call the proc from within the program, only one row gets updated. So When I call the proc from Query Analyser, all rows get updated. When I call the proc from within the program, only one row gets updated Any ideas as to why this is happening?? JobID Description Price Status ---------------------------------------------- 73412 Documents:Item 3 .00 0 73412 Documents:Item 5 .00 0 73412 Documents:Item 2 .00 0 73412 Documents:Item 4 .00 0 73412 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 4 73412 Documents:Item 5 .00 0 73412 Documents:Item 2 .00 0 73412 Documents:Item 4 .00 0 73412 Documents:Item 1 .00 0 Public Sub UpdateAllItems() As Boolean Dim objCnn As ADODB.Connection Dim objCmd As ADODB.Command Set objCnn = New ADODB.Connection With objCnn .ConnectionString = cnConn .CursorLocation = adUseClient .Open End With Set objCmd = New ADODB.Command Set objCmd.ActiveConnection = objCnn With objCmd .CommandText = "sp_UpdateJobItem" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@Status", adInteger, adParamInput, 4, Me.Status) .Parameters.Append .CreateParameter("@JobID", adInteger, adParamInput, 4, Me.iJobID) .Execute End With Set objCnn = Nothing Set objCmd = Nothing End Sub ----------------------------------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROCEDURE dbo.sp_UpdateJobItem @JobID As int , @Status As int AS -- ================================================== =========================================== SET XACT_ABORT OFF -- Allow procedure to continue after error DECLARE @error integer -- Local variable to capture the error OnHoldAction. -- ================================================== =========================================== BEGIN TRANSACTION UPDATE tbl_JobItems SET Status = @status WHERE JobID = @JobID -- ================================================== =========================================== -- Check for errors -- ================================================== =========================================== SELECT @error = @ERROR If @error > 0 BEGIN ROLLBACK TRANSACTION END Else BEGIN COMMIT TRANSACTION END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

推荐答案

> 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 will update every row with that JobID. How do you intend to determine which row to update? What is the key of this table? -- David Portas SQL Server MVP --

我正在使用jobID字段来确定要更新的记录。这是关键所在。 。因此，对于示例中的73412的给定jobid，应该更新具有该id的所有行 。 I''m using the jobID field to determine which records to update. That''s the key. So for a given jobid of 73412 like in the example, all rows with 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 SET ROWCOUNT 0 in the proc just before the UPDATE. -- David Portas SQL Server MVP --