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

Refreshing Views After DDL Changes

Eduardo Pivaral shows how you can refresh the metadata for a view in SQL Server after one of its underlying tables or functions changes: So we proceed to execute an alter view over the first view: ALTER VIEW dbo.[vi_invoices_received_by]ASSELECT ConfirmedReceivedBy as [Received by], COUNT(InvoiceID) as [# of Invoices], CustomerIDFROM Sales.InvoicesGROUP BY ConfirmedReceivedBy, CustomerID;GO So we […]

Read More

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data: This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set […]

Read More

Categories

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