SET NOEXEC ON

Andy Kelly shows how to prevent part of a script from running:

 

If we were to hit F5 (or however you execute your TSQL statements in SSMS) without highlighting any statement(s) they would all be executed, one batch after the other. Even if one batch were to fail or we had a THROW in that batch it would fail at that point but execution would continue immediately after the next GO until the end. This is where SET NOEXEC ON comes into play. If I add that at the beginning of the script all succeeding code would not be executed. The statements would only be compiled and not actually run. It would look like this:

SET NOEXEC ON;

PRINT ‘Got Here 1’ ;
GO
PRINT ‘Got Here 2’ ;
GO
PRINT ‘Got Here 3’ ;
GO
PRINT ‘Got Here 4’ ;
GO

This is a useful “accidental F5” protection:  you can put it at the top of your long script to keep from running the whole thing at once.

Related Posts

Multi-Pattern Replacement with SQL Server

Hugo Kornelis has a pattern matching problem to solve: The actual use case and the list of patterns that I had to remove are considered a confidential competitive advantage by my client, so I will just make up a list here to illustrate the problem. In this fake requirement, the following patterns must all be […]

Read More

Forensic Accounting: Cohort Analysis

I continue my series on forensic accounting techniques with cohort analysis: In the last post, we focused on high-level aggregates to gain a basic understanding of our data. We saw some suspicious results but couldn’t say much more than “This looks weird” due to our level of aggregation. In this post, I want to dig […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728