Click or drag to resize

SA0030 : Output parameter never assigned

The topic describes the SA0030 analysis rule.

Message

Output parameter never assigned

Description

The rule checks for not used stored procedure output parameters. Unused parameters not necessarily negatively affect the performance, but they just add bloat to your stored procedures and functions.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0030

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE PROCEDURE Production.uspGetList 
 2
 3      @Product varchar(40) 
 4        , @VendorCode varchar(20) -- parameter is not used
 5    , @MaxPrice money 
 6    , @ComparePrice money OUTPUT
 7    , @ListPrice money OUT
 8        , @ListPrice2 money OUT /*IGNORE:SA0030*/
 9    , @ComparePrice2 money OUTPUT
10    , @output int output
11    , @output2 int output
12    , @output3 int output
13    , @output4 int output
14    , @output5 int output
15AS
16    SET NOCOUNT ON;
17    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
18    FROM Production.Product AS p
19    JOIN Production.ProductSubcategory AS s 
20      ON p.ProductSubcategoryID = s.ProductSubcategoryID
21    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
22
23-- Populate the output variable @ListPprice.
24--SET @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
30-- Populate the output variable @ComparePrice.
31SET @ComparePrice = @MaxPrice;
32
33IF (@ComparePrice > 200.00)
34BEGIN
35    -- Parameter is assigned at least once in the procedure body
36    SET @ComparePrice2 = 5;  
37    RETURN 
38END
39
40IF 1 NOT IN (1, 2)
41BEGIN
42
43    RAISERROR('Invalid Type ''%s''', 16, 1, 'type');
44
45    IF (@ComparePrice2 > 5)
46    BEGIN
47          SET @output = 0;
48    END
49END;
50
51IF (@ComparePrice  = 200.00)
52BEGIN
53    RETURN 5;
54
55    -- Parameters are assigned, but it is after the RETURN statement.
56    SET @ListPrice = 1
57    SET @ComparePrice2 = 5;
58    SET @output = 1;
59END
60
61SET @output2 = 1
62
63BEGIN TRY
64    RAISERROR('Invalid Type ''%s''',9, 1, 'type');
65    SET @output3 = 1
66END TRY
67BEGIN CATCH 
68     SET @output5 = 4
69END CATCH 
70
71
72BEGIN TRY
73    RAISERROR('Invalid Type ''%s''',11, 1, 'type');
74    SET @output4 = 1
75
76END TRY
77BEGIN CATCH 
78    SET @output5 = 1
79END CATCH

Analysis Results

 MessageLineColumn
1SA0030 : The output parameter @ListPrice is never assigned.76
2SA0030 : The output parameter @output4 is never assigned.136
See Also

Other Resources