By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

ERROR_STATE() Always = 0?

P: n/a
Below is the contents of a SPROC I have. I want to return the error
info in the catch block for it so I call RaiseError. But the
ERROR_STATE() always comes up as 0 which is ilegal since it must be
between 1-127 I guess. So I wrote a stupid if block to set it to 1 to
stop that error.

But I'd really rather not do this. Can anyone tell what's wrong? Why am
I not getting a correct return from ERROR_STATE()?

Thanx much:

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM WebUser2Role WHERE WebUserID = @WebUserID

INSERT INTO WebUser2Role
SELECT value, @WebUserID FROM fIntList2Table(@RoleIDList)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

IF @ErrorState = 0
BEGIN
SET @ErrorState = 1
END

IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )

END CATCH

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
END

Mar 31 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The only thing I can think of which may help is that
Errors with a severity of 10 or lower are considered warnings or
informational messages, and are not handled by TRY.CATCH blocks.
therefore maybe it's not recognised as an error ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<wa********@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Below is the contents of a SPROC I have. I want to return the error
info in the catch block for it so I call RaiseError. But the
ERROR_STATE() always comes up as 0 which is ilegal since it must be
between 1-127 I guess. So I wrote a stupid if block to set it to 1 to
stop that error.

But I'd really rather not do this. Can anyone tell what's wrong? Why am
I not getting a correct return from ERROR_STATE()?

Thanx much:

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM WebUser2Role WHERE WebUserID = @WebUserID

INSERT INTO WebUser2Role
SELECT value, @WebUserID FROM fIntList2Table(@RoleIDList)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

IF @ErrorState = 0
BEGIN
SET @ErrorState = 1
END

IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )

END CATCH

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
END

Mar 31 '06 #2

P: n/a
No it deffinately is going into the catch block.

But ERROR_STATE() in the catch block returns 0. This poses a problem
when I call RaiseError because 0 is not a valid state.

Mar 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.