SQL Prompt is a useful SQL syntax Prompt tool. SQL Prompt automatically retrieves the database based on object names, syntax, and code snippets, providing users with appropriate code choices. Automatic script Settings make code simple and easy to read — especially useful when developers aren’t familiar with scripts. SQL Prompt can be installed and used to greatly improve coding efficiency.

Click to download the free VERSION of SQL Prompt

Louis Davidson reveals some useful hints for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedious work, and make testing easier, sometimes all three.

SQL Prompt is a tool I’ve been using for code completion and code formatting tasks, so much so that writing queries without it felt very wrong. Write SQL statements with JOIN, which automatically suggest ON conditions. In the ORDER BY or GROUP BY clause, it helps to select grouped columns. Review some ugly code (perhaps your own) and format it nicely in the style you like.

However, SQL Prompt has a number of other features that I rarely use but are useful when I need them. This tutorial demonstrates how SQL Prompt can help with “small-scale” SQL refactoring tasks, which are typically limited to a single batch of code or code objects that you work on locally. These tasks are performed directly in the SSMS query window and can help you refactor your code in a variety of ways that are useful when testing and troubleshooting stored procedures. This article is mainly about the first half of the tutorial

All of the examples in this article start with a newly restored copy of the WideWorld Printers sample database.

Rename aliases/variables

Tip: Place the cursor over the alias or variable, select “Rename Alias/Variable” from the context menu, or press F2 and enter a new name. All matches in the current batch or object have changed.

Sometimes programmers choose bad names for variables or aliases. I have seen the @ I, if check carefully, you can call it @ EnableSystemFunctionsSetting more effectively. We’ve been guilty time and time again of standards failing when you need to code quickly, and we often forget to pause and think carefully about the best possible name.

The following code shows an application. Cities$List stored procedure is followed by a second batch (just to demonstrate how this renaming works). Suppose you want to rename @cityName to @citynamelike to make it clear to users that they can pass LIKE expressions.

CREATE OR ALTER PROCEDURE Application.Cities$List
      @CityName nvarchar(50) = The '%', @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
  GOCopy the code

Listing 1

Of course, one tried and true method is find and replace (Ctrl + H). However, if you do not pay attention, it may cause some parameters renamed @ CityNameLikeUpperCaseFlag by accident, and in the current object beyond expected range of renaming parameters and variables.

Figure 1

Using the RENAME alias/variable refactoring of SQL Prompt, you can perform this task more precisely. Highlight the occurrence of @cityName, or place the cursor over it, which only highlights the other exact occurrence of @CityName in the current range. Unlike find and replace, it doesn’t highlight anything that doesn’t exactly match, and it doesn’t touch the second batch.

Figure 2

Now, right-click and select Rename alias/variable, or press F2, and when you see the variable in the rectangle, type the replacement name CityNameLike, and then press Enter to “submit” the name change. If you enter incorrectly, you can use a single undo (Ctrl-Z) to repair the damage.

Next, you may need to change the Cities table alias to a shorter name, such as C. If any instance of Cities is clicked, SQL Prompt will highlight the other instances, but because Cities is not an Alias, the Rename Alias/Variable method of SQL Prompt will have no effect. However, this is easy to fix. Simply create Cities as an alias, highlight it and click F2.

Figure 3

Replace Cities with C, click Enter, and the new code will look like this.

CREATE OR ALTER PROCEDURE Application.Cities$List
    @CityNameLike nvarchar(50) = The '%', @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;
GOCopy the code

Find unused variables and parameters

Tip: In the query window, you can choose to highlight code blocks, select “Find unused variables and parameters” from the SQL Prompt menu, or press (Ctrl B, Ctrl F), and Prompt will underline all unused variables or parameters.

In Figure 3, you can see a green wavy line @cityNamevalue (and the process name, more on that later) below. This is the SQL Prompt code analysis feature that alerts us to variables that are declared but never used.

Figure 4.

Even if you turn this off (and you sometimes do when using very large scripts), you can still use “Find unused variables and parameters” (Ctrl B + Ctrl F) and see the same green wavy line below the @CityNamevalue variable, and the same message. Let’s simply remove the variable declaration and move on to more improvements to the code.

Refactoring using snippets of code

Many of the built-in code snippets of SQL Prompt contain the $SELECTEDTEXT $placeholder. If you highlight any code in the query window and call one of these code segments, it inserts the selected text into a placeholder within the code segment. For example, we can insert any selected code:

  • The BEGIN… END block (by calling the BE fragment)

  • IF block (IFS)

  • The TRY… CATCH block (TC)

  • CTE (CTE)

  • Creating a View (CV)

  • Creating inline table Valued Functions (CITF)

I’ll show you a few examples and show you how to make your own code snippets, just like these, that you can call with a few keystrokes.

Add a BEGIN… END block

The other green pattern under the first line of code in the previous listing is another SQL Prompt “warning,” which prompts one of its built-in code analysis rules. In this case, this is a style rule (ST003) that recommends including the process body in BEGIN… In the END.

Figure 5

This is not a stored procedure requirement, but as the instructions in rule (ST003) tell us, multi-line user-defined functions require it, so it is a good practice to be consistent. Anyway, I think a BEGIN… The END block helps clarify what is code in an object and what is not.

To solve this problem, simply highlight the query during the procedure and then invoke the BE fragment from the SQL Prompt action menu. Or, for BEGIN… END, WHILE, and IF, you simply right-click the code and select “Surround Sound” (Ctrl-K, CtrL-S) from the context menu.

Figure 6.

Now, BAM, the process body is in a BEGIN… In the END block. While doing this, I also added a semicolon to the END statement using the insert semicolon operation.

CREATE OR ALTER PROCEDURE Application.Cities$List
      @CityNameLike nvarchar(50) = The '%', @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;Copy the code

Listing 3

Refactoring queries into code modules

SQL Prompt also makes it easy to refactor queries into reusable logic in the form of views, inline table-valued functions, or common table expressions. For example, highlight the query in Listing 4…

SELECT CustomerID,
         CustomerName,
         BillToCustomerID,
         CustomerCategoryID
  FROM   Sales.Customers;Copy the code

Listing 4

And invoke the CV (Create View) code snippet, and your query is now included at the beginning of the view definition.

CREATE VIEW [schema].[view_name]
  --WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA
  AS
      SELECT CustomerID,
             CustomerName,
             BillToCustomerID,
             CustomerCategoryID
      FROM   Sales.Customers;
  -- WITH CHECK OPTION
  GOCopy the code

Listing 5

This is a good reminder of the important part of the view definition, and it will work here once you name it, but in most cases, it’s just a basic starting point. Also, there is no syntax validation, so you can happily call the CV fragment to convert Listing 6 to a view, even if it doesn’t compile, because there are no variable declarations, and even if there are, there are no variables allowed in the view.

SELECT CustomerID,
         CustomerName,
         BillToCustomerID,
         CustomerCategoryID
  FROM   Sales.Customers
  WHERE  @CustomerId = 1;Copy the code

Listing 6

Instead, you may need to use the “Create inline Table Function” (CITF) code 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; )
  GOCopy the code

Listing 7

Again, this is a useful start for creating functions, especially since I often forget the syntax for creating functions of any type, but of course, you still need to deal with the @customerID variable and take it as a parameter if you need to.

Refactoring using custom fragments

You can easily build your own snippets to refactor a piece of code. For example, one thing I often need to do is comment out large chunks of code with multi-line comments.

This is a very simple custom snippet. To create it, simply browse the SQL Prompt | Snippet Manager… | New… Add an initial name and description for the code snippet, and then enter the code snippet code. After creating this code, I just need to highlight the block to comment out, select “co” from the “prompt action” list, and the text will be included in the comment, and I will also see who commented out the text, and when.

Figure 7.

I can see many other uses for such snippets, such as variable declarations (how many times did I create @msg to hold a THROW message?).

Inline execution: Refactor for testing

Tip: In the query window, highlight the EXEC command, scalar function, or sp_executesQL function for the stored procedure, and then select Inline EXEC from the right-click content menu. SQL Prompt replaces it with the equivalent inline code.

Suppose we want to test the application Cities$List stored procedure, so we code the call to the procedure as follows:

EXECUTE Application.Cities$List @CityNameLike = 'Nash%'Copy the code

Listing 8

This returns a lot of data, and you want to take a deeper look at the code because something doesn’t feel right. Highlight Cities$List, right-click and select Inline Exec. The parameters of the procedure will be converted to variables, and the variable values you passed in will be used (or the default if you have omitted a parameter, as I did), as shown 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;Copy the code

Listing 9.

This may not be the most complex refactoring to do, but it is a common and tedious task, especially if you have many parameters and one or more have default values.

Abstract

SQL Prompt contains many great tools to help you refactor your code, all of which are small and easy to use every day. As a programmer, it’s sometimes hard to rely on more than just find and replace, but the more I find tools like these in SQL Prompt, the more useful I find them when things aren’t so simple.