When developing T-SQL code, I frequently have a number of stored procedures created and run inside one .sql file. While I’m generally good at highlighting the single statement I want to run, there are some situations where the highlight is misplaced – usually by me getting complacent with the lack of lag on Remote Desktop. Suddenly I’ll get a dose of lag, my highlight command (shift-uparrow) won’t be processed, but my Execute (F5) command will.
To defend against this, I determined that the best option would be to create a severe error at the top of the .sql file to ensure that execution would cease. Unfortunately, this only stops the execution of the current batch, and as CREATE PROCEDURE must be at the start of the batch, I have many batches in my code.
Five minutes after requesting suggestions from Twitter, Rob Farley suggested using SQLCMD mode – which is a feature that I haven’t used very often. This is activated in Management Studio via the “Query†| “SQLCMD Mode†menu item, and can be specified as the default mode for all new query windows.
Placing the following code at the top of the file results in a fatal error that completely halts execution:
:on error exit
SELECT * FROM SomeTableThatDoesntExist
GO
!!dir
GO
The !!dir command will list the contents of the current directory. You can run this line by itself, but this line won’t be executed if you run the entire .sql file.
The drawback to this method is that you lose Intellisense in SQL Server 2008 and the ability to debug your code, but I’m willing to live with this to prevent having to comment out my more dangerous statements!