SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Prompt Tips #5 - Current Statement Execution

By Steve Jones, (first published: 2015/01/12)

This might be one of my favorite new features in SQL Prompt. I thought it was automatic semi-colons, but after using this for a couple weeks, I love this feature. It's something I wouldn't have thought of, but I have found it to be incredibly handy to me. I expect this is a tip that I'll use extensively in demo situations as well since it can prevent embarassing mistakes.

Oops

How many times have some of you had code on your screen like this:

Someone asked you to preform some DML, but before you do so, you want to check the code. What you really want to do is highlight the code like this, and then execute one statement.

However, you accidentally press F5, or CTRL+E, or my favorite, ALT+X, and all of a sudden you have updated an order, which might be incorrect. What's more, you might not have the old data available and you've compromised data integrity. I've done this before, often because I'm handling a request for someone while I'm doing some other work and not completely paying attention.

Imagine if this is a delete statement, perhaps one I've started writing with no WHERE clause, and I'm checking data and accidentally execute the entire screen of code.

Ugh.

Current Statement Execution

Fortunately the SQL Prompt team added a new feature to v6.4 that can help. It's called Current Statement Execution, and it works like this. Let's imagine that I have two pieces of code:

If I execute the second item, I'll see there's not data in my table. I've recorded this on video as well to help you visualize how this works. What I'd like to do is highlight my second snippet of code and only highlight that, as shown here.

However, that's a pain. I can easily mis a line, or mess up it the mouse, or any number of things that result in me not highlighting things correctly. What's more, I'm not coding quickly, which is what SQL Prompt was built to help you improve.

With SQL Prompt v6.4, I can do this:

By pressing Shift+F5, I'll execute the current statement only. That's the one that has my cursor in it. In any line, any part of the query. By doing that, I'll execute the code and it will get highlighted by SQL Prompt.

I'll have my SELECT query run, without the INSERT being executed when I Shift+F5.

CAVEAT

In this case, nothing is returned, but the code is highlighted. However, I wanted to test something, thinking this was a strange looking highlight. I pressed CTRL+E, and nothing changed on my screen in the code window, but in the results I got a row.

What happens is SQL Prompt highlights the code, but only with a background color change. It isn't highlighted in SSMS as you would highlight code with the cursor or SHIFT+arrow keys. That's a little disturbing, and disconcerting since at first glance it seems like code is highlighted. It's not.

Pressing F5 again (without the Shift), got me this:

Here's the highlight in a light green, not the expected blue. Be careful with that.

There's also a video version of this tip.

 
Total article views: 3288 | Views in the last 30 days: 4
 
Related Articles
BLOG

Execute the Current Statement–SQL Prompt

This is a great little feature in SQL Prompt that I wrote about at SQLServerCentral. It’s Current st...

ARTICLE

SQL Prompt Tips #7 - Begin End Highlighting

This SQL Prompt tip shows you how code highlighting makes it easier to find which items match up wit...

FORUM

Running Jobs from Command Prompt

Executing jobs from Command Prompt

FORUM

SSIS package with Variable which prompts while executing

SSIS package with Variable which prompts while executing

Tags
sql prompt    
 
Contribute