0

I'm wondering if there is a way to terminate SQL process when an error is raised an catched in Azure datawarehouse.

For example:

BEGIN TRY
    PRINT 'a';
    THROW 60000,'xyz',1
    PRINT 'b'
END TRY

BEGIN CATCH
    Print 'catched'
END CATCH

Print 'c'
Print 'd'

The output of the current code is: a, catched, c, d.

The desired output is a, catched. Also I'll be wrapping the try-catch block inside a while loop and printing 'c', 'd' will be outside it.

I tried "SET NOEXEC OFF", but is not working in Azure datawarehouse.

EDIT: The entire logic will be in a stored procedure

Can anyone please help me with this?

Thank you!

sunil kumar ampolu
  • 13
  • 3

2 Answers2

0

Why can't you move Print 'c' Print 'd' inside the Try block? Another option is to use a Transaction block. This would be extreme, but there is this option as well: https://stackoverflow.com/a/804811/8104777

Caleb George
  • 234
  • 1
  • 10
  • Thank you for the reply. I want print 'c', print 'd' outside the try block. I understood that I can use Transactions, but the operations (Switching partitions) I'm supposed to perform can't be used inside a transaction. Please let me know if there is any other way. Thank you! – sunil kumar ampolu Jun 05 '21 at 16:45
0

Add return to your catch block to terminate execution:

BEGIN TRY
    PRINT 'a';
    THROW 60000,'xyz',1
    PRINT 'b'
END TRY

BEGIN CATCH
    Print 'catched' -- Caught.
    return
END CATCH

Print 'c'
Print 'd'
HABO
  • 15,314
  • 5
  • 39
  • 57
  • Thank you for the reply. It did worked, but I want the entire logic to be wrapped inside a stored procedure and it is not allowing as the return statement should be inside UDF. Is there any way the same functionality can also be implemented inside SPROC? Thank you! – sunil kumar ampolu Jun 05 '21 at 16:41
  • @sunilkumarampolu You can use [`return`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15) within a stored procedure and, in that usage, it can also return an `int` as a status value. A [user-defined function](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15) is not a stored procedure, though `return` is used in multi-statement UDFs to return a result. Is the "stored procedure" in your question a "UDF"? – HABO Jun 05 '21 at 17:09
  • 2
    @HABO the RETURN command is not supported on Azure SQL Data Warehouse or its successor Azure Synapse Analytics dedicated SQL pools. – wBob Jun 05 '21 at 17:56