This article discusses the origin of the so-called current statement execution functionality for running one SQL statement where the cursor currently resides. Furthermore, it explains how it works in productivity extension for SQL Server Management Studio and Visual Studio, ApexSQL Complete.
An option for the current statement execution is frequently looked-for among DBAs and developers. This functionality is similar to the well-known F5 command for running the entire SQL statement if nothing is selected or the selected portion of the code in the query editor.
Although running the entire script is fine for many, some will argue that if a script contains several SQL statements aka batches, an option for running only a single SQL statement is desirable as having to select parts of code by clicking and holding the mouse is bad ergonomics for some.
Current statement execution
Let’s head over to SQL Server Management Studio and see how to use this option in ApexSQL Complete.
The following example shows two identical code examples in the sample AdventureWorks2012 database:
USE AdventureWorks2012; GO SELECT * FROM Production.Product ORDER BY Name ASC; SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO
When this code is run, it retrieves two result sets because of the two SELECT statements:
Now, we could for example run only the first statement by clicking and selecting the code while holding the left mouse click. Then, of course, we need to click either Execute button or press the F5 (default) command from the keyboard.
This time, SQL Server retrieves only one result set because of the one selected SQL statement:
Here is an easier way to achieve the same in just two clicks.
Right-click anywhere within the first statement, and click the Run current statement option from the context menu as shown below:
ApexSQL Complete analyzes the code and will run an individual statement in the script where the cursor currently resides.
In the example above, the right-click was registered on line 5, but the cursor could be placed anywhere within the statement. The statement was then executed and, as can be seen below, results are returned only for the first SELECT statement:
Q: Does ApexSQL Complete use a delimiter-terminated mechanism to separate individual statements in a batch?
A: No, it does not. The Run current statement option lets you run an individual SQL statement regardless of where SQL statements in the script are ended with a valid delimiter (;).
For example, if the cursor is placed at line 5 and the Run current statement command is used, it will only execute the first SELECT statement regardless of the missing semicolon:
Q: Is the executed individual statement marked in the query editor in some way?
A: No, it is not. Currently, there is no visual aid except for the cursor positioning indicator in the query editor.
Q: What statement will be run if the cursor is placed on an empty line between the two statements?
A: If for example the cursor is positioned on line 7 which is between the two SELECT statements, the above statement will be run then the Run current statement command is used: