Detecting T-SQL code smells – Part 3

This is The third and last part of the Detecting T-SQL code smells series. Within this post I will cover how the solution can be customized by adding new validation rules

Detecting T-SQL code smells – custom rules

Before implementing new validation rules, I would like to cover some scenarios which are already supported by default:

Scenario 1:

• Find stored procedures missing SET NOCOUNT ON
• Find statements using SELECT *
• Exclude a file using the whitelist

Note: I will be using the sample files as shipped with the Codeplex project.

Steps that to be performed:

• Create a folder for the SQL files (I’m calling this “sql”)
• Create a separate folder for the configuration files (Naming it “configuration” to keep all simple)
• Create a copy of errors.txt and WhiteListFiles.txt within the “configuration” folder
• Open errors.txt and make sure it only contains the following:

ErrorNum,Type,Description,Severity
 0,Err,Parsing Error,10
 5,BP,Avoid use of 'Select *',10
 30,BP,Include SET NOCOUNT ON inside stored procedures,3

• Open WhiteListFiles.txt, and add ConvertDateMultiCond.sql, which is the file we would like to exclude.
• Execute the script and provide the folder locations;

PS D:\TSQLSmells> .\TSQLSmells.ps1 -FolderPath  D:\TSQLSmells\test\sql -ConfigFolder D:\TSQLSmells\test\configuration

As you can see, it’s pretty simple to create a custom “template” and only show the issues of your interest.

Scenario 2:

• Make the White List optional by default
• Find all statements that include WAITFOR
• Find all statements that include WHILE

I’m not using the Whitelist option as much and therefore prefer it to be optional which is easy to accomplish by replacing the existing code by the version below.

Just search for the first like and you will find the original error message and the part responsible forcing the script to stop processing.

Finding statements including WAITFOR and WAITFOR isn’t possible by default and we therefore have to add some new detection logic to get this working.

First it’s required to create a new .sql sample file with the following content and make sure to save this file within the correct folder so it will be processed later.

Within the PowerShell script, search for the function Process-Statement and add $StmtType allowing us to see the object type name which we need to use later. Executing the script at this point should output some additional details within the shell, including the WhileStatement and WaitForStatement. Now that we know the object type name, we can extend the solution;

At this point it’s required to add the new errors within the errors.txt file. Just append the following lines:

1001,BP, WhileStatement,10
1001,BP, WaitForStatement,10

Running the script should result in the following:

code smell gui

Just keep in mind that extending the project like this isn’t very scalable. A better alternative would be to create a custom function for every existing function you would like to extend, and add all your custom validation within that function.