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

  • 1. Download and install ASR Pro
  • 2. Launch the program and select your language
  • 3. Follow the onscreen instructions to complete the setup process
  • Click here to fix your computer now with this fast

    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.

    PROCEDURE

      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 0 .

    How can you check both this special @@ error and the number of affected lines in such a reliable case?

    @@error vs @@rowcount

    @@ 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)

    Syntax

      @@ ERROR 

    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) .

    Examples

    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.

    @@error vs @@rowcount

      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

    Recommended: ASR Pro

    Is your computer running slow and sluggish? Are you worried about losing your important files and data? Then look no further than ASR Pro – the ultimate solution for fixing all of your Windows-related issues. This powerful software will repair common errors, protect your system from disaster, and optimize your PC for maximum performance. So don't wait any longer – download ASR Pro today and enjoy a smooth, speedy, and worry-free computing experience!

  • 1. Download and install ASR Pro
  • 2. Launch the program and select your language
  • 3. Follow the onscreen instructions to complete the setup process

  • 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 

    See Also

    TRY … CATCH (Transact-SQL)
    ERROR_LINE (Transact-SQL)
    ERROR_MESSAGE (Transact-SQL)
    ERROR_NUMBER (Transact-SQL)
    ERROR_SEVERITY (transact-sql)
    Error Procedure (Transact-SQL)
    ERROR_STATE (Transact-SQL)
    @@ ROWCOUNT (Transact-SQL)
    sys.messages (Transact-SQL)
    Errors and Events (Database Help System)

    • Article
    • 3 minutes read.

    Click here to fix your computer now with this fast