How Can I Stop a SQL Server Script from Running Prematurely?

Early termination of script execution in SQL Server

In SQL Server, you may need to stop the execution of a script early if some validation or lookup in the script logic fails. Here are two ways to achieve this:

1. RAISERROR method

The

raiserror statement can be used to terminate script execution by raising an error with severity level 20 or higher and specifying the WITH LOG option. An example is as follows:

<code class="language-sql">raiserror('发生致命错误', 20, -1) with log</code>

This will terminate the current connection, preventing the rest of the script from running. Please note that this method requires sysadmin role permissions.

2. NOEXEC method

Another method to use with the GO statement is to set noexec on. This will disable execution of the remaining scripts. Example:

<code class="language-sql">print 'hi'
go

print '致命错误，脚本将不会继续！'
set noexec on

print 'ho'
go</code>

This method does not terminate the connection, but skips the execution of any subsequent code until noexec is closed again. In SQL Server Management Studio, you may need to issue the set noexec off command to resume execution.

When using these methods, please keep the following considerations in mind:

• RAISERROR requires sysadmin privileges and may cause you to lose your database connection.
• If you are not logged in as sysadmin, RAISERROR will fail and the script will continue execution.
• NOEXEC does not require sysadmin permissions, but you must explicitly disable it before executing subsequent code.

The above is the detailed content of How Can I Stop a SQL Server Script from Running Prematurely?. For more information, please follow other related articles on the PHP Chinese website!