Using @@ROWCOUNT in TRY CATCH block

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using @@ROWCOUNT in TRY CATCH block

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2008-09-09 : 06:26:59

Hi,

I'm using TRY CATCH block for error handling and reporting but the @@ROWCOUNT does not seem to work here.

My Code:


BEGIN TRY

	SELECT TOP 10 *
	FROM [TableName]

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
	/* log message */
		--ERROR Log Message Section
		SELECT @intRowCount = @@ROWCOUNT, @intErrorCode = ERROR_NUMBER(), @intErrorMessage = ERROR_MESSAGE();
Insert all the above in a log message table
				RAISERROR (@chrMessageFailure , 16, 1)
				RETURN @intErrorCode
END CATCH;
/* log message */
--SUCCESS Log Message Section
SELECT @intRowCount = @@ROWCOUNT;
Insert all the above in a log message table

Now in log messages table, the row count is coming up as 0 whereas it should be 10. Any suggestions will be much appreciated.

Thanks

umertahir
Posting Yak Master

154 Posts

Posted - 2008-09-09 : 06:42:22

OK I cracked it by introducing another INT variable in and using is instead of @@ROWCOUNT


BEGIN TRY

	SELECT TOP 10 *
	FROM [TableName]


SELECT @intRowCount = @@ROWCOUNT

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
	/* log message */
		--ERROR Log Message Section
		SELECT @intRowCount = @@ROWCOUNT, @intErrorCode = ERROR_NUMBER(), @intErrorMessage = ERROR_MESSAGE();
Insert all the above in a log message table using @intRowCount
				RAISERROR (@chrMessageFailure , 16, 1)
				RETURN @intErrorCode
END CATCH;
/* log message */
--SUCCESS Log Message Section
SELECT @intRowCount = @@ROWCOUNT
Insert all the above in a log message table using @intRowCount

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 07:43:35

Or just switch the two SELECT statements in the CATCH section?
What the first SELECT (error_message) is doing, is reseting @@ROWCOUNT to 1 because the SELECT only returns 1 row.

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-09-09 : 08:41:02

quote:
Originally posted by Peso

Or just switch the two SELECT statements in the CATCH section?
What the first SELECT (error_message) is doing, is reseting @@ROWCOUNT to 1 because the SELECT only returns 1 row.

E 12°55'05.63"
N 56°04'39.26"

Yup, that's another way of doing it. cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources