Anti-Oopsie Tip of the Day

We’ve all done this. You know you have. Here’s the problem:

When scripting a Stored Procedure, you don’t save the script. You execute the script to create the stored procedure. Then you throw the script away, and execute the stored procedure.  However, quite the opposite is true when you are scripting a query or task in a query window. In this case, to run the thing you do in fact have to execute the script to run it.

This causes me no end of “oopsie opportunities”. I work a lot in stored procedures, so I’m very accustomed to thinking “Execute to Save”. But when I start writing a script to do a lengthy ad-hoc update, or a mass delete, or to kick off a lengthy backup procedure, hitting “Execute to Save” will give me a nasty suprise: the script starts doing it’s nefarious deed – immediately!

Here’s a tip:

At the top of your ad-hoc, one-off scripts, make the very first line “RETURN”. And be sure to code the rest of it using NO “GO” Statements (explained in a moment).

That way, if you inadvertently hit the “Execute” button, all it does is run and then immediately end. Then, to actually run the script, you highlight everything EXCEPT the first line, and execute that. And off it goes, doing what you want, but most importantly, doing it as planned.

The reason you need to NOT insert any GO statements is that each “GO” creates a seperate “batch” in your script. If there is a GO statement, anywhere in the script, then the “RETURN” on the top line will only make it jump to the first line after the very first GO statement and start executing there. That could be even worse than what you are trying to avoid, so use this tip with caution.

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in S.W.A.T.. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s