SQL Server - T-SQL : raisError

raisError, not raiseError!

The function is spelled wrong: raisError rather than raiseError.

The following causes: Msg 102, Level 15, State 1… Incorrect syntax near 'raiseError':

raiseError('raiseError should be raisError!!!', 16, 1);

String concatenation

In raiserror, the message string cannot be concatenated with +. Instead, a printf approach needs to be used:

raisError('A string: %s, a number: %d.', 16, 1, 'Hello world', 42);

Github repository about-MSSQL, path: /t-sql/functions/raiserror/string-concatenation.sql

Use within functions

raisError cannot be used within user defined functions (Invalid use of a side-effecting operator 'RAISERROR' within a function).

Exiting an SQL script

If loged in with the sysadmin role (admin), raisError() can be used to completely exit an SQL script so that it also skips subsequent SQL batches. In order for this to work, the severity level needs to be at least 20 and the with log option needs to be used.

In the following example, an SQL script uses db_name() to verify if it is connected to a database named TQ84_DB. If this is not the case, the executing process is terminated (See generated message).

Note, that also the next batch which would print I am in another batch is not executed (because the session is terminated).

--
-- Check if connected to database TQ84_DB/
-- If not, terminate process:
--
if db_name() != 'TQ84_DB' begin
   raisError('Wrong database', 20, -1) with log;
   --
   -- Message will be somthing like
   --    Msg 2745, Level 16, State 2, Line 2
   --    Process ID 55 has raised user error 50000, severity 20. SQL Server is terminating this process.
   --    Msg 50000, Level 20, State 1, Line 2
   --    Wrong database
   --    Msg 596, Level 21, State 1, Line 0
   --    Cannot continue the execution because the session is in the kill state.
   --    Msg 0, Level 20, State 0, Line 0
   --    A severe error occurred on the current command.  The results, if any, should be discarded.
end

print('db_name successfully checked');

go

print('I am in another batch.');

go

Github repository about-MSSQL, path: /t-sql/functions/raiserror/exit-SQL-script.sql

Because this method requires sysadmin privileges, another way to achieve a similar result might be set noexec on.

More details in this Stackoverflow answer.

See also the sqlcmd statement :on error exit instructs sqlcmd to stop processing an SQL script file when it encounters an error.