0

In MSSQL, neither SELECT queries that return no rows nor INSERT\UPDATE\DELETE queries that affect no rows are treated as an exception. In my tSQL code, there are plenty of situations where an empty result set is a perfectly acceptable outcome that should not interrupt flow of control. When appropriate, I can perform a pre-check query or use post-query @@ROWCOUNT to check for 0, 1, or >1 results and generate an exception using THROW or RAISERROR.

My question is what is the reasoning for NO DATA FOUND being a predefined exception in Oracle? Is it due to technical constraints (it "must" be done this way), or is there a more theoretical reason (it "ought" to be done this way)? Please help me appreciate Oracle's approach!

To be clear, there are plenty of resources on HOW to handle this exception, I want to know WHY.

1

1 Answer 1

3

According to the SQL standard, each SQL command (statement) sets status parameters, including SQLSTATE, upon its completion (or termination). "No data" is one of the possible values of SQLSTATE, distinct from the "successful completion" value.

It is an implementation choice whether to treat that particular SQLSTATE as an exceptional state or success. Apparently, SQL Server, at least in some cases, chooses the latter, while many other DBMSes do the former.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.