SA0179 : Do not create function and procedures with too many parameters

When SQL procedures or functions have too many parameters, it often indicates that they are performing too many tasks or that there is a missed opportunity to structure the parameters more effectively, such as wrapping them in a composite type like a table or user-defined type.

For example:

 1-- Example of a problematic procedure with many parameters
 2CREATE PROCEDURE PerformMultipleTasks
 3    @Param1 INT,
 4    @Param2 INT,
 5    @Param3 INT,
 6    ...
 7    @ParamN INT
 8AS
 9BEGIN
10    -- Procedure logic
11END;

This can be problematic because managing a high number of parameters can:

• Increase the complexity of procedure calls and maintenance.

• Indicate a violation of the single-responsibility principle, making the procedure hard to optimize and debug.

• Suggest that the use of a better data structure could simplify parameter management.

Refactor procedures or functions with excessive parameters to improve design and maintainability.

Follow these steps to address the issue:

1. Review the stored procedure or function parameters to understand their purpose and usage. Identify any parameters that can be grouped together or eliminated.

2. Refactor the SQL procedure or function by considering combining related parameters into a single table or a user-defined type. This can help in reducing the number of parameters.

3. Evaluate whether the procedure is performing multiple tasks. If so, consider splitting it into smaller procedures, each with a more focused role, complying with the single-responsibility principle.

For example:

 1-- Example of a refactored procedure using a user-defined table type
 2CREATE TYPE TaskParameters AS TABLE
 3(
 4    Param1 INT,
 5    Param2 INT,
 6    Param3 INT
 7);
 8
 9CREATE PROCEDURE PerformMultipleTasks
10    @TaskParams TaskParameters READONLY
11AS
12BEGIN
13    -- Procedure logic using parameters from @TaskParams
14END;

The rule has a Batch scope and is applied only on the SQL script.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Code Smells

There is no additional info for this rule.

Other Resources