How to throw message and commit transaction using RAISERROR?

Question

I need to use RAISERROR to throw a message(pop up message) and commit that transaction.Is there any option?

For severity 18 the transaction got rollback.I have changed the severity to 10 and tried like

RAISERROR('Your Reference Number is %s',10,0,@param);

this but it commits the transaction but doesnt show message. What i need here is Message has to be thrown and transaction has to be commited

Any suggestion?

score 5 · Answer 1 · edited May 23 '17 at 12:21

Don't use exceptions to pass back "OK" messages. You wouldn't in a c# or java program. Exception means "I've aborted because SHTF"

You'd use thsi to return meaningful data

SELECT 'Your Reference Number is ' + @param

In a typical template (from my answer Nested stored procedures containing TRY CATCH ROLLBACK pattern?)

SET XACT_ABORT, NOCOUNT ON


BEGIN TRY

    BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    COMMIT TRANSACTION

    SELECT 'Your Reference Number is ' + @param

END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH

What does "my front end doesnot accept this" mean? Can you elaborate? — MatBailie, Jul 22 '11 at 15:06

score 5 · Answer 2 · answered Jul 22 '11 at 15:35

RAISERROR with severity above 10 will be treated like an exception by the ADO.Net client. Depending on how your call context looks like, this may or may not rollback the transaction. If you use a SqlTransaction or a TransactionScope in the client, or a BEGIN TRY/BEGIN CATCH block on the server side, this will likely roll back the transaction. Point is that RAISERROR neither commits nor rolls back the transaction, is your own code that rolls back or commits and we cannot know what you're doing from your post.

RAISERROR with severity bellow 10 will be considered a informational message and not cause an exception. See Database Engine Error Severities. This is probably why you say that the 'it doesn't show the message' (whatever that means). Client side frameworks treat the informational messages differently, for instance ADO.Net will raise an SqlConnection.InfoMessage event on the connection but will not raise an exception. You probably don't have anything set up in your application for this event and your code is simply ignoring the info messages. For example how to use the InfoMessage event see Connection Events (ADO.NET)

**RAISERROR with severity above 10 will be treated like an exception by the ADO** saved me :) The code was using severity 1 and hence catch block was not called. — TheTechGuy, Nov 08 '14 at 04:32

score 0 · Answer 3 · answered Jul 22 '11 at 14:02

0

It sounds like you need to use the WITH NOWAIT parameter for RAISERROR - this will output it to the message window immediately:

RAISERROR('Your Reference Number is %s',10,0,@param) WITH NOWAIT

answered Jul 22 '11 at 14:02

JNK

63,321
15
122
138

it is throwing message but the transaction got rollback – bala3569 Jul 22 '11 at 14:12
That's the **OPPOSITE** of what you said in your original problem: `it commits the transaction but doesnt show message`. Which is it? – JNK Jul 22 '11 at 14:13
what i need is to throw message and commit that transaction – bala3569 Jul 22 '11 at 14:14

How to throw message and commit transaction using RAISERROR?

3 Answers3