@@error @@rowcount
Contents |
Microsoft Tech Companion App Microsoft Technical Communities Microsoft Virtual Academy Script Center Server and Tools Blogs TechNet Blogs TechNet Flash @@error in sql server example Newsletter TechNet Gallery TechNet Library TechNet Magazine TechNet Subscriptions TechNet Video db2 sql error TechNet Wiki Windows Sysinternals Virtual Labs Solutions Networking Cloud and Datacenter Security Virtualization Downloads Updates Service sql server @@error message Packs Security Bulletins Windows Update Trials Windows Server 2012 R2 System Center 2012 R2 Microsoft SQL Server 2014 SP1 Windows 8.1 Enterprise See all trials » Related sql server error code Sites Microsoft Download Center TechNet Evaluation Center Drivers Windows Sysinternals TechNet Gallery Training Training Expert-led, virtual classes Training Catalog Class Locator Microsoft Virtual Academy Free Windows Server 2012 courses Free Windows 8 courses SQL Server training Microsoft Official Courses On-Demand Certifications Certification overview MCSA: Windows 10 Windows Server Certification (MCSE) Private Cloud Certification (MCSE)
@@rowcount In Sql Server
SQL Server Certification (MCSE) Other resources TechNet Events Second shot for certification Born To Learn blog Find technical communities in your area Support Support options For business For developers For IT professionals For technical support Support offerings More support Microsoft Premier Online TechNet Forums MSDN Forums Security Bulletins & Advisories Not an IT pro? Microsoft Customer Support Microsoft Community Forums United States (English) Sign in Home Library Wiki Learn Gallery Downloads Support Forums Blogs We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Accessing and Changing Database Data Procedural Transact-SQL Handling Database Engine Errors Handling Database Engine Errors Using @@ERROR Using @@ERROR Using @@ERROR Retrieving Error Information in Transact-SQL Using TRY...CATCH in Transact-SQL Using RAISERROR Using PRINT Using @@ERROR Handling Errors and Messages in Applications TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is no
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of
Sql Error 803
this site About Us Learn more about Stack Overflow the company Business ms sql error Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask t-sql @@error Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up https://technet.microsoft.com/en-us/library/ms190193(v=sql.105).aspx Using @@error and @@rowcount in stored procedures up vote 0 down vote favorite My stored procedure accepts xml data, the number of rows that can be generated from this xml data is unknown forehand. In this I need to handle transactions when saving this xml data to a table. I cannot use @@rowcount to check whether the insert is successful because all rows http://stackoverflow.com/questions/22740163/using-error-and-rowcount-in-stored-procedures in the xml are inserted at once. Therefore the only option I have is @@error. Assume the xml contains 10 rows of data, if one record failed to insert would @@error detect it? Thanks sql sql-server xml tsql share|improve this question edited Mar 30 '14 at 7:06 marc_s 450k918621027 asked Mar 30 '14 at 4:15 AndrewMC 106110 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted Not knowing the actual INSERTs, but in general the answer is yes. And if it's a single statement and it errors, there will be no rows inserted after rollback; otherwise it depends on your error handling code. I'd recommend using TRY .. CATCH to handle errors instead of @@ERROR function. There is a very good article by Erland Sommarskog about error handling in SQL Server you should read: http://www.sommarskog.se/error-handling-I.html#batchabort1 And a newer one as well: http://sommarskog.se/error_handling_2005.html share|improve this answer answered Mar 30 '14 at 5:34 dean 6,5591617 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this http://stackoverflow.com/questions/13356775/tsql-transaction-checking-both-error-and-rowcount-after-a-statement site About Us Learn more about Stack Overflow the company Business Learn more http://blog.paulhadfield.net/2010/11/sql-server-checking-rowcount-and-error.html about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up TSQL transaction sql server checking both @@ERROR and @@ROWCOUNT after a statement up vote 4 down vote favorite I can rollback a transaction if an error occurs: CREATE PROCEDURE [dbo].[MySproc] ( @Param1 [int] ) AS BEGIN TRAN SET NOCOUNT ON; SELECT @Param1 UPDATE [dbo].[Table1] SET Col2 = 'something' WHERE Col1 = @Param1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN -12 END ... But if there are no in sql server entries in the Table1 with the Col1 = @Param1 transaction commits successfully, which is bad for me. I'd like to check if Col2 = 'something' is really done. If not, then rollback with a particular return code. If I try to insert @@rowcount check after @@error check, like the following: CREATE PROCEDURE [dbo].[MySproc] ( @Param1 [int] ) AS BEGIN TRAN SET NOCOUNT ON; SELECT @Param1 UPDATE [dbo].[Table1] SET Col2 = 'something' WHERE Col1 = @Param1 IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN -12 END IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRAN RETURN -27 END ... it always rollbacks at this point, because @@rowcount evaluates the very last statement so it always equals 0. How in such a case to check both the @@error and the number of rows affected? sql-server tsql stored-procedures error-handling transactions share|improve this question edited Nov 13 '12 at 7:33 marc_s 450k918621027 asked Nov 13 '12 at 7:09 rem 5,4802785154 add a comment| 1 Answer 1 active oldest votes up vote 8 down vote accepted Store both into your own variables in a single query, then check: DECLARE @rc int DECLARE @err int SELECT @Param1 U
Coded Row'IF(@@ERROR <> 0)Print 'Error Occured'ELSE IF(@@ROWCOUNT = 0)Print 'No Rows Affected'The error occurs because the reference to @@ERROR in the first "IF" statement counts as a SQL statement; resetting the value held in @@ROWCOUNT. As the second "IF" statement checking @@ROWCOUNT is only evaluated if the first "IF" statement (@@ERROR) it will always return true! Note: Reversing the order of the two IF statements would hide any potential errors, as @@ERROR would be reset upon checking @@ROWCOUNT.The safest way to evaluate this statement is to SELECT the contents of @@ERROR and @@ROWCOUNT into local variables within a single statement and then check the values of the local variables, in other words:DECLARE @ErrorCode INTDECLARE @RowsAffected INT SELECT 'Hard Coded Row'SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT IF(@ErrorCode <> 0)Print 'Error Occured'ELSE IF(@RowsAffected = 0)Print 'No Rows Affected'Problem solved! Posted by Paul Hadfield at 21:06 Labels: SQL Server No comments: Post a Comment Newer Post Older Post Home Twitter Tweets by @Paul_Hadfield Blog Archive ► 2016 (5) ► March (2) ► February (3) ► 2015 (2) ► October (1) ► May (1) ► 2014 (4) ► April (1) ► January (3) ► 2013 (2) ► April (1) ► January (1) ► 2012 (32) ► December (7) ► November (6) ► October (3) ► September (1) ► August (1) ► July (3) ► April (1) ► February (10) ► 2011 (13) ► October (1) ► August (2) ► April (1) ► February (4) ► January (5) ▼ 2010 (23) ► December (2) ▼ November (1) SQL Server: Checking @@ROWCOUNT and @@ERROR ► October (2) ► September (10) ► August (5) ► July (3) Labels .NET v4.0 (5) .NET v4.5 (3) Agile (4) Android (1) Android Studio (1) Automapper (3) C# (7) C# Exceptions (1) Career (5) Castle Windsor (4