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