ERROR_STATE() Always = 0?

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
ENDThe 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

<wackyphill@.yahoo.com> wrote in message
news:1143771643.520517.319500@.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|||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.