Hello,

When you look in the manual

http://dcx.sybase.com/index.html#sa160/en/dbreference/raiserror-statement.html

It says "Transact-SQL -- The RAISERROR statement cannot be used in Transact-SQL compound statements and procedures"

So then I assume it can only be used in the watcom dialect, I later notices that after raiserror the execution in the procedure continues, and for me that is not a wanted behaviour.

So then I found this

http://dcx.sybase.com/index.html#sa160/en/dbadmin/continue-after-raise-error-option.html

and to my great surprise it says

"The setting of the continue_after_raiserror option is used to control behavior following a RAISERROR statement only if the on_tsql_error option is set to Conditional (the default). If you set the on_tsql_error option to Stop or Continue, the on_tsql_error setting takes precedence over the continue_after_raiserror setting"

So I have clearly missunderstood something can someone please explain?

But in a watcom store procedure (if I had set set option public.continue_after_raiserror = 'OFF') I do get the behaviour I want (stop to execute after raiserror), but perhaps you should not use raiserror like this?

Thanks in advance

MG

asked 13 Jul '15, 10:24

M%20G's gravatar image

M G
629253044
accept rate: 42%


I don't know if this topic is still of concern ... it does look like a dead ended discussion . . . but just in case it is of interest to someone . . .

So let me start by clearing up one misconception. RAISERROR is a Transact SQL statement! As such it can be used inside of T/SQL stored procedures and batches. The noted section of the docs seems to be incorrect and may be referring to use in Watcom SQL or Standard SQL ... I'm not actually certain ... it was added around the time Try-Catch was added and there are restrictions on using the 2 related options and raiserror in the Try block so it may be in deference to that [issue has been reported to the doc team]

For Transact SQL procedures RAISERROR's behavior is well defined Error Handing in Transact-SQL procedures and needs to be for a degree of compatibility with other database servers that also use T/SQL. But, as with anything technical and evolving, variations do occur.

Historically, RAISERROR was added to SQL Anywhere after Watcom SQL become part of Sybase's offerings {along with much of the rest of ASE's Transact SQL} and was never part of the Watcom SQL dialect so mixing it up with W/SQL and/or Std SQL PSM code is an area for Caveat Utilitor to apply. Internally RAISERROR is based (at least partially) on Signals so you may be able to find it works outside of the T/SQL more than our officially stated support for it ... but do test, then test some more. [Do watch out for sea monsters and dragons that may pop up, though]

Otherwise . . . Welcome to the world or mix-dialects! (and in places where 'the map makers of olde' would have resorted to drawing dragons, you will find many warnings, many more caveats and some strange behavior and interactions; see "How Transact-SQL is supported" ) This is something that can be confusing even to experienced SQL practitioners and baffling to novices.

Best of luck.

permanent link

answered 18 Aug '15, 14:09

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

IMO the phrase "should not use" sums it up :)

Besides being spelled incorrectly (it should be RAISEERROR) the RAISERROR statement is usually too polite to be useful... and it is certainly too complex.

The SIGNAL statement is highly recommended.

Also, the ROLLBACK TRIGGER WITH RAISERROR is very useful, in spite of the fact the word "RAISERROR" appears.

Also highly recommended: Section 9.5 Error Handling in my book :)

permanent link

answered 15 Jul '15, 12:04

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

well I do not have your book, so I can not look it up right now.

So instead I have to continue to ask questions and hope that someone will answer ;)

Well the signal is not an option for me, I need an error which looks like the error generated automatically by the database (for example when you try to insert a NULL value in a colum which not permits NULLS). The Error should be thrown back and be managed the same way by the layer on top on the database. Right now we have an automatical error handling on that layer which takes care of all automatically generated errors from the database and this error handling system could not handle SIGNAL, but RAISERROR is taken cared of in a correct manner. Just to add this error handling is nothing we developed it is something that is out of the box and we "should" use....

So therefore I am kind of stuck (for now) with the RAISERROR option...

So therefore I need clarifications regarding what I have read in the manual can I use it the way I intended? When I test it is seems to work but could there be some bumps futher down the road because I chose an option which was not intended to be used like this?

Thanks in advance

MG

(16 Jul '15, 04:30) M G
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×94
×1

question asked: 13 Jul '15, 10:24

question was seen: 4,874 times

last updated: 18 Aug '15, 14:09