@@ ROWCOUNT值为零

本文介绍了@@ ROWCOUNT值为零的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个存储过程，我在表t1中插入值，并在表t1中插入值后更新表t1中的列c1。 我是使用两个整数标量变量@ v1和@ v2来获取对两个标量变量使用@@ ROWCOUNT影响的行数。 它适用于insert语句但返回0表示更新声明

DECLARE @ VersionNumber VARCHAR （ 100 ） DECLARE @ ReleaseDate DATE DECLARE @ Created_By VARCHAR （MAX） DECLARE @ IsActive INT DECLARE @ ResponseText VARCHAR （MAX） SET @ VersionNumber = ' 1.0.2.0' SET @ ReleaseDate = ' 01-Aug-2013' SET @ Created_By = ' christopherf' SET @IsActive = 1 SET @ResponseText = ' ' BEGIN SET NOCOUNT ON ; DECLARE @ Update1 SMALLINT DECLARE @ Update2 SMALLINT DECLARE @ Ref_DeskTimeCurrentVersion_ID INT IF （ @ VersionNumber <> ' '） BEGIN IF NOT EXISTS （ SELECT * FROM DeskTime_CurrentVersion DCV WHERE LOWER（DCV.VersionNumber）= LOWER （ @ Version数字）） BEGIN IF （ @IsActive = 1 ） BEGIN IF （（ SELECT COUNT（*） FROM DeskTime_CurrentVersion）> = 1 ） BEGIN INSERT INTO DeskTime_CurrentVersion （VersionNumber，ReleaseDate，Created_By，Created_DateTime，IsActive，Flag） VALUES （ @ VersionNumber ， @ ReleaseDate ， @ Created_By ，GETDATE（）， @ IsActive ， 1 ） IF （ @@ ROWCOUNT = 1 ） BEGIN SET @ Update1 = 1 结束 SELECT @ Ref_DeskTimeCurrentVersion_ID = IDENT_CURRENT（' DeskTime_CurrentVersion'） UPDATE DeskTime_CurrentVersion SET IsActive = 0 ，标志= 0 ， Last_Updated_By = @ Created_By ， Last_Updated_Da teTime = GETDATE（） WHERE Ref_DeskTimeCurrentVersion_ID<> @ Ref_DeskTimeCurrentVersion_ID IF （ @@ ROWCOUNT = 1 ） BEGIN SET @ Update2 = 1 END IF （ @ Update1 = 1 AND @ Update2 = 1 ） BEGIN SET @ ResponseText = ' ADDED' END END ELSE BEGIN INSERT INTO DeskTime_CurrentVersion （VersionNumber ，ReleaseDate，Created_By，Created_DateTime，IsActive，Flag） VALUES （ @ VersionNumber ， @ ReleaseDate ， @ Created_By ，GETDATE（）， @IsActive ， 1 ） IF （ @@ ROWCOUNT = 1 ） BEGIN SET @ Update1 = 1 END IF （ @ Update1 = 1 ） BEGIN SET @ ResponseText = ' ADDED' END END END ELSE BEGIN INSERT INTO DeskTime_CurrentVersion （VersionNumber ，ReleaseDate，Created_By，Created_DateTime，IsActive，Flag） VALUES （ @ VersionN umber ， @ ReleaseDate ， @ Created_By ，GETDATE（）， @IsActive ， 1 ） IF （ @@ ROWCOUNT = 1 ） BEGIN SET @ ResponseText = ' ADDED' END END END ELSE BEGIN SET @ ResponseText = ' 版本号alrea dy存在' END END ELSE BEGIN SET @ ResponseText = ' 提供的版本号无效。 END PRINT @ ResponseText END

解决方案

OP确认的实际解决方案 - 没有数据匹配 Ref_DeskTimeCurrentVersion_ID<> @Ref_DeskTimeCurrentVersion_ID 一旦输入数据，问题就解决了。

亲爱的Chris， 在这种情况下您的情况可能会失败 Last_Updated_By = @Created_By，Last_Updated_DateTime = GETDATE（）

I have a stored procedure in which i am inserting value in table t1 and after inserting values in table t1 updating a column c1 in table t1. And am using two integer scalar variable @v1 and @v2 to get count of rows affected by using @@ROWCOUNT for both scalar variables. It works fine for insert statement but returns 0 for update statement

DECLARE @VersionNumber VARCHAR(100) DECLARE @ReleaseDate DATE DECLARE @Created_By VARCHAR(MAX) DECLARE @IsActive INT DECLARE @ResponseText VARCHAR(MAX) SET @VersionNumber = '1.0.2.0' SET @ReleaseDate = '01-Aug-2013' SET @Created_By = 'christopherf' SET @IsActive = 1 SET @ResponseText = '' BEGIN SET NOCOUNT ON; DECLARE @Update1 SMALLINT DECLARE @Update2 SMALLINT DECLARE @Ref_DeskTimeCurrentVersion_ID INT IF(@VersionNumber <> '') BEGIN IF NOT EXISTS(SELECT * FROM DeskTime_CurrentVersion DCV WHERE LOWER(DCV.VersionNumber) = LOWER(@VersionNumber)) BEGIN IF(@IsActive = 1) BEGIN IF((SELECT COUNT(*) FROM DeskTime_CurrentVersion) >= 1) BEGIN INSERT INTO DeskTime_CurrentVersion (VersionNumber,ReleaseDate,Created_By,Created_DateTime,IsActive,Flag) VALUES (@VersionNumber,@ReleaseDate,@Created_By,GETDATE(),@IsActive,1) IF(@@ROWCOUNT = 1) BEGIN SET @Update1 = 1 END SELECT @Ref_DeskTimeCurrentVersion_ID = IDENT_CURRENT('DeskTime_CurrentVersion') UPDATE DeskTime_CurrentVersion SET IsActive = 0, Flag = 0, Last_Updated_By = @Created_By, Last_Updated_DateTime = GETDATE() WHERE Ref_DeskTimeCurrentVersion_ID <> @Ref_DeskTimeCurrentVersion_ID IF(@@ROWCOUNT = 1) BEGIN SET @Update2 = 1 END IF(@Update1 = 1 AND @Update2 = 1) BEGIN SET @ResponseText = 'ADDED' END END ELSE BEGIN INSERT INTO DeskTime_CurrentVersion (VersionNumber,ReleaseDate,Created_By,Created_DateTime,IsActive,Flag) VALUES (@VersionNumber,@ReleaseDate,@Created_By,GETDATE(),@IsActive,1) IF(@@ROWCOUNT = 1) BEGIN SET @Update1 = 1 END IF(@Update1 = 1) BEGIN SET @ResponseText = 'ADDED' END END END ELSE BEGIN INSERT INTO DeskTime_CurrentVersion (VersionNumber,ReleaseDate,Created_By,Created_DateTime,IsActive,Flag) VALUES (@VersionNumber,@ReleaseDate,@Created_By,GETDATE(),@IsActive,1) IF(@@ROWCOUNT = 1) BEGIN SET @ResponseText = 'ADDED' END END END ELSE BEGIN SET @ResponseText = 'Version number already exists' END END ELSE BEGIN SET @ResponseText = 'Invalid version number provided.' END PRINT @ResponseText END

解决方案

Actual solution confirmed by OP - there was no data matching where Ref_DeskTimeCurrentVersion_ID <> @Ref_DeskTimeCurrentVersion_ID Once data was entered the problem was resolved.

Dear Chris, your condition might be failed on this condition Last_Updated_By = @Created_By, Last_Updated_DateTime = GETDATE()