Raise an error if a subquery returns no result (MS SQL SERVER)-CodePudding

In DB2 it is possible to raise an error if a subquery returns no results with the help of coalesce and the raise_error scalar function.

But how can this be done in sql server?

I checked scalar functions so far, but there is no raise error function which can be used in a single query.

The use case is about finding a matching value in another table during an export of millions of records. So, for every record in the query a matching value is looked up. The idea is to raise an error if there is no matching value for a record so it is detected early and not after the whole query is run.

example for db2 (note: the subquery needs to be replaced by something meaningful...):

SELECT 
    COALESCE(
        (SELECT 1 FROM SYSIBM.SYSDUMMY1 LIMIT 0), 
        RAISE_ERROR('70NUL', 'Value is missing')) 
FROM 
    SYSIBM.SYSDUMMY1;

CodePudding user response：

This is the solution I'll continue with:

   SELECT COALESCE ((select 1), 'Query aborted. This is the error message' / 0);
   -- returns 1

   SELECT COALESCE ((select null), 'Query aborted. This is the error message' / 0);
   -- query aborts showing message

   SELECT COALESCE ((select null WHERE 1=2), 'Query aborted. This is the error message' / 0);
   -- query aborts showing message

CodePudding user response：

You are looking for RAISEERROR:

RAISERROR ('Oups', 1, 1)

EDIT

As Lamu pointed out, it is also possible to use THROW, as in

    PRINT 'In catch block.';  
    THROW;