Find Stored Procedures that are not using all the Parameters Passed into them. SQL Server.

This last week we discovered a bug in a stored procedure. We were passing a parameter into a stored procedure used to do an insert, but the value was not used in the actual insert statement, and thus the value was not stored in the database table. The problem was easy to fix, but as with all bugs, I ask if there is a way we can prevent the bug from recurring. We came up with an SQL statement that we can run to identify all the stored procedures that have unused parameters into them. We then set up a unit test to run every night to let us know if any of our stored procedures have this flaw. Here is the stored procedure that should work on any version of SQL Server from 2005 on up.

SELECT ROUTINE_NAME, P.PARAMETER_NAME
FROM INFORMATION_SCHEMA.ROUTINES R
INNER JOIN
INFORMATION_SCHEMA.PARAMETERS P
ON P.SPECIFIC_NAME = R.ROUTINE_NAME
WHERE ( (LEN(OBJECT_DEFINITION(OBJECT_ID(R.ROUTINE_NAME)) ) - LEN(REPLACE(OBJECT_DEFINITION(OBJECT_ID(R.ROUTINE_NAME)) , P.PARAMETER_NAME, ''))) - LEN(P.PARAMETER_NAME) = 0)
ORDER BY 1,2

The clever piece of this query is that we use the Information_Schema.Parameters to get a list of all the parameters in each stored procedure, and then we replace all occurrences of the parameter name in the stored procedure with an empty length string. If the length of the stored procedure prior to our substitution and subtracting the length of the parameter name just once is equal to zero, then we know the parameter only occurs once, in the input, and that it is not used within the body of the stored procedure.

Note, you may want to include an additional clause in the where clause if you want to exclude some of the system stored procedures.