Over the past few days, some users have come across the well-known error message with @@ error vs @@ rowcount. This problem can arise for several reasons. Let’s take a look at them now.
Recommended: ASR Pro
Use. SQL Server @@ ROWCOUNT is a functional variable used to offset the number of rows that might be affected by the last confirmation of a package. It will also likely be used for error handling to check the number of runs affected in the instruction.
create [dbo]. [MySproc]( @ Param1 [number])AS? 'OR' WHATSTART TRANSACTIVATED;SELECT nocount @ Param1UPDATE [dbo]. [Table 1]SET Col2 matches' something'O Col1 = @ Param1IF @@ ERROR <> 0TO BEGIN RETURN TRANSPORT RETURN -12END...When
but usually there are no records in my
table1 with a successful
Col1 transaction equal to @ Param1 , which is terrible for me. It’s like checking if
Col2 = 'something' is actually being executed. If not, return with a specific return code.
When I pulled to insert the
@@ rowcount check right after the
@@ error check like:
CREATION PROCEDURE [dbo]. [MySproc]( @ Param1 [number])AS? 'OR' WHATSTART TRANSACTIVATED;SELECT nocount @ Param1UPDATE [dbo]. [Table 1]SET Col2 = 'something'O Col1 = @ Param1IF @@ ERROR <> 0TO BEGIN RETURN TRANSPORT RETURN -12ENDIF @@ ROWCOUNT means 0TO BEGIN RETURN TRANSPORT RETURN -27END...Is always
it is reset at this point because
@@ rowcount is evaluating the most recent statement, so this tool is always
How can you check both this special
@@ error and the number of affected lines in such a reliable case?
@@ Rowcount was previously used to display the number of rows affected by the last SQL statement.
@@ Error gives the error number of the last statement executed by SQL. If there is no error, the value is usually zero.
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Back To Types
returns the error number as soon as an error occurred in the previous statement. If the error is an error in the sys.messages collection view, @@ error will contain the price in the sys.messages.message_id column for that particular error. You can view the written text associated with the @@ ERROR by entering the sys.messages number.
What is the value of @@ error when a Trappable error occurs?
When a certain correctable error occurs, the @@ ERROR function is set to a value greater than 0.
Since the @@ error is cleared and cleared each time the statement is executed, find it directly for the statement to test, or store it in almost any local variable that can be viewed later.
What does @@ mean in SQL Server?
In SQL Server, the @@ character precedes environment variables. Undoubtedly, the server manages all the global variables. The global variable for these people: starts with the @@ prefix. You don’t need to declare things like that because the server entertains these people all the time. These are system functions and clients cannot declare them.
Use TRY … CATCH to handle errors. Const The TRY … CATCH clause also recommends additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY and ERROR_STATE) that return all information about a package error in the form @@ ERROR. TRY … CATCH supports the ERROR_NUMBER function, which is not limited to frequently returning an error number in an instruction immediately and immediately to an instruction representing a technical error. For more information TRY see … CATCH (Transact-SQL) .
A. Use @@ ERROR To Identify The Corresponding Error
What is the use of @@ error in SQL?
@@ ERROR returns the error number of executed T-SQL statements. It returns 1 if the previous Transact-SQL statement did not qualify any errors, otherwise a significant error number is returned.
The following example uses
@@ ERROR to check in the
UPDATE statement to see if a validation constraint violation (bug # 547) has occurred.
USE AdventureWorks2012;WALKUPDATE HumanResources.EmployeePayHistory SET payout frequency = 4 O BusinessEntityID implies 1;IF @@ ERROR = 547 TO BEGIN PRINT No limit check quote. '; ENDWALK
B. Conditional Use Of @@ ERROR To Execute Correct Procedure
The following example uses
IF ... else ,
@@ ERROR statements after the excellent
DELETE statement in Test work can be performed on the injured procedure. The value of the
@@ ERROR variable determines the return of code sent to your current caller that indicates success or failure of the procedure.
USE AdventureWorks2012;WALK- Delete the procedure, it may already exist.IF OBJECT_ID (N'HumanResources.usp_DeleteCandidate ', N'P') IS NOT NULL PROCEDURE FOR DELETING HumanResources.usp_DeleteCandidate;WALK- Create your own process.PROCEDURE FOR CREATING HumanResources.usp_DeleteCandidate ( @IDCandidate INT )AS? 'OR' WHAT- Execute the DELETE statement.REMOVE HumanResources.JobCandidate O JobCandidateID = @CandidateID;- Check the error value.IF @@ ERROR <> 0 TO BEGIN - Return 99 to one of our callers to indicate an error. PRINT N 'Error while deleting candidate information. '; RETURN 99; ENDDIFFERENT TO BEGIN - Returns 0 directly to the caller, display is normal. PRINT N 'Candidate deleted.'; RETURN 0; END;WALK
B. Using @@ ERROR With @@ ROWCOUNT
The following example uses
@@ ERROR with
@@ ROWCOUNT which can check the work an exclusive
UPDATE statement. The value
@@ ERROR can be checked for individual error indicators, and
@@ ROWCOUNT is used to ensure that the update was applied to a row from its table without any problem.
USE AdventureWorks2012;GO OBJECT_ID (N'Purchasesif.usp_ChangePurchaseOrderHeader ', N'P') NOT NULL SEND purchase procedure .usp_ChangePurchaseOrderHeader;GO PROCEDURECreate Purchasing.usp_ChangePurchaseOrderHeader ( @PurchaseOrderID INT , @ BusinessEntityID INT )AS? 'OR' WHAT- Declare the details to be used in error checking.DECLARE @ErrorVar INT; @RowCountVarDeclare INT; - As you can see, execute the UPDATE statement.UPDATE purchase order header SET BusinessEntityID = @BusinessEntityID WO PurchaseOrderID matches @PurchaseOrderID; - Only write the @@ ERROR and @@ ROWCOUNT values â€‹â€‹locally.- Variables long before they are deleted.SELECT @ErrorVar equals @@ ERROR , @ RowCountVar = @@ ROWCOUNT; - Check for errors. If an incorrect @BusinessEntityID is specified,- The UPDATE statement returns almost any foreign key violation error # 547.IF @ErrorVar <> 0 TO BEGIN IF @ErrorVar = 547 TO BEGIN PRINT N'ERROR: Invalid identificationOor to work with a new employee. '; RETURN 1; END DIFFERENT TO BEGIN PRINT ERROR NO.: Error ' + RTRIM (CAST (@ErrorVar AS NVARCHAR (10))) + N 'happened.'; RETURN 2; END END - Check the number of lines. @RowCountVar is 0- but if an incorrect @PurchaseOrderID is specified.IF @RowCountVar = 0 TO BEGIN PRINT 'Warning: the required BusinessEntityID is invalid'; RETURN 1; ENDDIFFERENT TO BEGIN SEAL "updated order due to a new employee"; RETURN 0; END;WALK
TRY … CATCH (Transact-SQL)
Error Procedure (Transact-SQL)
@@ ROWCOUNT (Transact-SQL)
Errors and Events (Database Help System)
- 3 minutes read.