Refactoring a Batch or Object With SQL Prompt

The author reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three.

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 ORDERBY or GROUPBY 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

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 with 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

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.

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

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

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.

Listing 5

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.

Listing 6

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.

Listing 7

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 snippet 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

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 and 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

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.

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's 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.