SQL Prompt is a tool I use all the time for code completion and code formatting tasks, to the point of it feeling very wrong to write a query without it. Write a SQL Statement with a
JOIN, and it auto-suggests the
ON criteria. At the
BY clause, it helps choose the grouping columns. Reviewing some ugly code (perhaps self-penned)? Format it nicely in your preferred style.
However, SQL Prompt has many other features that I use less often, but are nevertheless invaluable, when needed. This article demonstrates how SQL Prompt helps with ‘small-scale’ SQL refactoring tasks, the scope of which is generally limited to a single batch of code, or code object, on which you are working locally. These tasks are performed right within the SSMS query window, and help you refactor code in various ways that are extremely useful when testing and troubleshooting stored procedures.
In a subsequent article, I’ll cover ‘grand scale’ refactoring tasks that change one or more objects automatically, such as to name of a procedure, table or column. These changes can affect many other dependent objects, and therefore potentially other users, in the database.
All the examples in this article start from a freshly restored copy of the WideWorldImporters sample database.
Sometimes programmers choose poor names for variables or aliases. I’ve seen variables called
@I that, on closer inspection, could more helpfully have been called something like
@EnableSystemFunctionsSetting. We’re all guilty now and again; standards tend to lapse when you need to code quickly, and we forget to pause, now and again, and think carefully about the best possible name to use.
Listing 1 shows an
Application.Cities$List stored procedure followed by a second batch (just to demonstrate how this renaming works). Let’s say you want to rename
@CityNameLike to make it clear to the user that they can pass in a
CREATE OR ALTER PROCEDURE Application.Cities$List @CityName nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS DECLARE @CityNameValue nvarchar(50); SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityName; GO DECLARE @CityName int; SELECT @CityName GO
One tried-and-almost true method is, of course, find-and-replace (Ctrl+H). However, if you’re not paying attention, you could end up some parameters accidentally renamed to
@CityNameLikeUpperCaseFlag, and renaming parameters and variables outside your intended scope of the current object.
With SQL Prompt’s Rename Alias/Variable refactoring you can perform this task with a lot more precision. Highlight an occurrence of
@CityName, or place the cursor on it, and it highlights just the one other exact occurrence of
@CityName that is within the current scope. Unlike find-and-replace, it does not highlight any non-exact matches and it does not touch the second batch.
Now, right-click choose Rename Alias/Variable, or press F2, and when you see the variable enclosed in a rectangular box, type in the replacement name,
CityNameLike, and hit Enter to ‘commit’ the name change. If you’ve made a mistake, you can repair the damage using a single Undo (Ctrl-Z).
Next, you may want to change the alias of the
Cities table to something shorter like
c. If you click on any of the instances of
Cities, SQL Prompt will highlight the other occurrences, but since
Cities is not an alias, SQL Prompt’s Rename Alias/Variable method will not work. This is easily fixed, though. Just establish
Cities as an alias, then highlight it and hit F2.
Cities with c, hit Enter and the new code will look as shown in Listing 2.
CREATE OR ALTER PROCEDURE Application.Cities$List @CityNameLike nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS DECLARE @CityNameValue nvarchar(50); SELECT c.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(c.CityName) ELSE c.CityName END, c.Location FROM Application.Cities AS c WHERE c.CityName LIKE @CityNameLike; GO
Find Unused Variables and Parameters
In Figure 3, you can see a green squiggly line under
@CityNameValue (and the procedure name, but more on that later). This is the SQL Prompt Code Analysis feature alerting us to a variable that is declared but never used.
Even if you turn this feature off (which I do occasionally, when working with very large scripts), you can still use Find Unused Variables and Parameters (Ctrl B + Ctrl F) and you’ll see the same squiggly green line underneath
@CityNameValue variable, and the same the message. Let’s simply delete that variable declaration and move on to more improvements in this code.
Refactoring using code snippets
Many of SQL Prompt’s built-in snippets include the $SELECTEDTEXT$ placeholder. If you highlight any code in a query window and invoke one of these snippets, it will insert the selected text into the placeholder within the snippet. For example, we can insert any selected code into:
BEGIN…ENDblock (by invoking the be snippet)
- CTE (cte)
- Create View (cv)
- Create Inline Table Valued Function (citf)
I’ll demonstrate just a couple of examples and indicate how you can make your very own snippet that you can call with a couple of keystrokes, just like these.
Adding a BEGIN…END block
The other green squiggle, under the first line of code in previous listings, is another SQL Prompt ‘warning’ that the code flouts one of its built-in code analysis rules. In this case it’s a style rule (ST003) recommending enclosing the procedure body within
This isn’t a requirement for stored procedures, but as the description in the rule (ST003) tells us, it is required for multi-line user-defined functions, so it is a good practice to be consistent. In any case, I think a
BEGIN…END block helps make it clearer what code is and isn’t part of the object.
To fix the problem, simply highlight the query within the procedure and invoke the be snippet from SQL Prompt’s Action menu. Alternatively, for
IF, you can just right click the code and choose Surround With (Ctrl-K, Ctrl-S) from the context menu.
Now, bam, the procedure body is within a
BEGIN...END Block. While I was at it, I also used the Insert Semicolons action to add a semicolon to the
CREATE OR ALTER PROCEDURE Application.Cities$List @CityNameLike nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS BEGIN SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityNameLike; END;
Refactoring a query into a code module
SQL Prompt also makes it easy to refactor queries into reusable logic, in the form of a view, inline table valued function or common table expression. For example, highlight the query in Listing 4…
SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers;
…and invoke the cv (Create View) snippet, and your query is now enshrined into the beginnings of a view definition.
CREATE VIEW [schema].[view_name] --WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA AS SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers; -- WITH CHECK OPTION GO
This is a nice reminder of the important parts of a view definition and will work fine here, once you’ve given it a name, but in most cases it’s only a basic start. Also, there is no syntax validation, so you could quite happily invoke the cv snippet to turn Listing 6 into a view, even though it won’t compile, because there is no variable declaration, and even if there was, there are no variables allowed in views.
SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers WHERE @CustomerId = 1;
Instead, you’ll probably want to use the Create Inline Table Function (citf) snippet.
CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, ... AS RETURN ( SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers WHERE @CustomerId = 1; ) GO
Again, this is a useful start to creating your function, especially since I often forget the syntax of creating any sort of function, but of course you still need to handle the
@CustomerId variable and make it a parameter, if it is desired.
Refactoring with custom snippets
You can easily build your own snippets to refactor a section of code. For example, one thing I regularly need to do is to comment out large blocks of code, using the multiline comment notation.
Here’s a very simple custom snippets to do just that. To create it just navigate SQL Prompt | Snippet Manager…| New…, give the snippet an initialism and description, and enter the snippet code. Once this is created, I’ll just need to highlight the block I want to comment out, select co from Prompts Action list, and the text will be encased in a comment, and I’ll also see who commented out the text, and when.
There are quite a few other uses I can see for such snippets such as for variable declarations (how many times have I created
@msg to hold a message for a
Inline Exec: refactoring for testing
Say we want to test our
Application.Cities$List stored procedure, so we code up a call to the procedure, as follows:
EXECUTE Application.Cities$List @CityNameLike = 'Nash%'
This returns a lot of data, and you want to look deeper at the code, because something feels wrong. Highlight
Cities$List, right-click and choose Inline Exec. The parameters of the procedure will be transformed into variables, and the variable values you have passed in (or the default value, if you have omitted a parameter, as I have), will be used, as demonstrated in Listing 9.
DECLARE @CityNameLike1 nvarchar(50), @CityNameUpperCaseFlag1 bit; SET @CityNameLike1 = 'Nash%'; SET @CityNameUpperCaseFlag1 = NULL; BEGIN SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag1 = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityNameLike1; END;
This may not be the most complex refactoring one needs to do, but it is a common and tedious task, particularly when you have lots of parameters and one or more have default values.
SQL Prompt contains a lot of nice tools to help you refactor your code, in small ways that are easy to apply daily. As a programmer, sometimes it is hard not to simply rely on find-and-replace, but the more I discover tools like these, in SQL Prompt, the more I find them useful when the easy way of doing things is not so easy.