Click or drag to resize

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

The topic describes the SA0179 analysis rule.

Message

Do not create function and procedures with too many parameters

Description

The rule checks procedures and functions for being created with too many parameters.

This often indicates that the procedure or function does too many things or else its the parameters can be wrapped in a new structure.

Scope

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

Parameters
NameDescriptionDefault Value
MaxNumberOfParameters

Maximum number of parameters.

10

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1CREATE PROCEDURE Production.uspGetList 
 2
 3      @Product varchar(40) 
 4        , @VendorCode varchar(20) -- parameter is not used
 5        , @VendorCode2 varchar(20) -- parameter is not used, but the rule is suppressed because this comment contains 'IGNORE:SA0029' text.
 6    , @MaxPrice money 
 7    , @ComparePrice money OUTPUT
 8    , @ListPrice money OUT
 9    , @input int -- parameter is only assigned, but not used,
10    , @param8 int 
11    , @param9 int 
12    , @param10 int 
13    , @param11 int 
14AS
15    SET NOCOUNT ON;
16
17    SET @input = 0 
18    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
19    FROM Production.Product AS p
20    JOIN Production.ProductSubcategory AS s 
21      ON p.ProductSubcategoryID = s.ProductSubcategoryID
22    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
23-- Populate the output variable @ListPprice.
24SET @ListPrice = (SELECT MAX(p.ListPrice)
25        FROM Production.Product AS p
26        JOIN  Production.ProductSubcategory AS s 
27          ON p.ProductSubcategoryID = s.ProductSubcategoryID
28        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
29-- Populate the output variable @compareprice.
30SET @ComparePrice = @MaxPrice;

Analysis Results

 MessageLineColumn
1SA0179 : The procedure has too many parameters.10
See Also

Other Resources