• SQL Prompt
• Exploring and Refactoring Databases

Refactoring a Batch or Object with SQL Prompt

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 ORDER BY or GROUP 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.

Rename Alias/Variable

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 @CityName to @CityNameLike to make it clear to the user that they can pass in a LIKE expression.

Listing 1

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.

Figure 1

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.

Figure 2

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.

Figure 3

Replace Cities with c, hit Enter and the new code will look as shown in Listing 2.

Listing 2

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.

Figure 4

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:

• A BEGIN…END block (by invoking the be snippet)
• IF block (ifs)
• TRY…CATCH block (tc)
• 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 BEGIN…END.

Figure 5

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 BEGIN…END, WHILE, and IF, you can just right click the code and choose Surround With (Ctrl-K, Ctrl-S) from the context menu.

Figure 6

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 END statement.

Listing 3

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…

Listing 4

…and invoke the cv (Create View) snippet, and your query is now enshrined into the beginnings of a view definition.

Listing 5

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.

Listing 6

Instead, you’ll probably want to use the Create Inline Table Function (citf) snippet.

Listing 7

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.

Figure 7

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 THROW statement?)

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:

Listing 8

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.

Listing 9

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.

Summary

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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more