Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things you can do to make your life easier is to have clean code – without unnecessary and unused parameters and values.
Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup. That’s assuming you can afford the luxury of downtime.
How to make sure the parameters/variables are not used in code
To find unused parameters in stored procedures and functions, you can use the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views. They store the parameters and names of the stored procedures and functions that were used. To list all routines and all parameters, use this code:
SELECT ROUTINE_NAME, P.PARAMETER_NAME FROM INFORMATION_SCHEMA.ROUTINES AS R INNER JOIN INFORMATION_SCHEMA.PARAMETERS AS P ON P.SPECIFIC_NAME = R.ROUTINE_NAME;
When you have this information, you can write code that will count the number of parameter occurrences for each parameter in the object definition. If it is equal to 1, the parameter has only been used once, i.e. only defined and not used afterwards.
ApexSQL Refactor is a SQL Server management Studio and Visual Studio add-in that finds unused variables and parameters and cleans up your code by identifying and removing parameters and variables that aren’t used. It can also format SQL queries, refactor SQL code, database tables and much more.
To find unused parameters:
- Open or input code you want to check in the current query editor
- In the SQL Server Management Studio / Visual Studio menus, select the Unused variables and parameters from the ApexSQL Refactor menu:
After the process of finding unused parameters and variables is finished, the unused parameters will be underlined. You can mouse-over them to see a tooltip that clarifies the parameter/variable:
Finding the parameters and variables that are only declared, but not actually used in code can be quite easy. Use ApexSQL Refactor to clean up your code and remove unnecessary bloat.
April 4, 2013