Click or drag to resize

SA0115 : Ensure variable assignment from SELECT with no rows

The topic describes the SA0115 analysis rule.

Message

Ensure variable assignment from SELECT with no rows

Description

A common mistake that's made is that a SELECT statement will always assign to the variables in the SELECT. This isn't the case if the SELECT statement doesn't return a row. In this case, safeguards need to be created.

Standard

Ensure that the case where a SELECT statement won't assign values to variables is handled properly.

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:SA0115

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE PROCEDURE [dbo].[proc_SqlEnlight_Test_SA0115]
 2  @input int,
 3  @input2 int = 5,
 4  @output int output
 5AS
 6
 7SELECT @output = UpdatedWho, @input2 = 6 FROM dbo._asBlockHist WHERE UpdatedWho = @input;
 8SELECT @output
 9
10-- Case 1: Always referenced using ISNULL
11DECLARE @Supplier CHAR(5)
12SELECT @Supplier = Supplier FROM MDID_TRAN.dbo.TrOrderPO
13WHERE PoNum = '10101234'
14SELECT ISNULL(@Supplier, 'XXXXX')
15
16-- Case 2: Not ensured assigned but not used
17DECLARE @NotEnsuredAssignedSupplier1 CHAR(5)
18--SET @NotEnsuredAssignedSupplier1 = 'XXXXXX'
19SELECT @NotEnsuredAssignedSupplier1 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
20--SELECT @NotEnsuredAssignedSupplier1
21
22-- Case 3: Default value assigned
23DECLARE @Supplier2 CHAR(5)
24SELECT @Supplier2 = 'XXXXXX'
25SELECT @Supplier2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
26SELECT @Supplier2
27
28-- Case 4: Not handled - should generate SA0115 rule violation
29DECLARE @NotEnsuredAssigned CHAR(5)
30-- SET @NotEnsuredAssigned = 'XXXXXX'
31SELECT @NotEnsuredAssigned = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
32SELECT @NotEnsuredAssigned
33
34-- Case 5: Not handled but will be ignored
35DECLARE @NotEnsuredAssigned1 CHAR(5)
36SELECT @NotEnsuredAssigned1 = Supplier /*IGNORE:SA0115*/ FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
37SELECT @NotEnsuredAssigned1
38
39-- Case 6:Default value assigned
40DECLARE @EnsuredAssigned2 CHAR(10) = 'DEFAULT'
41SELECT @EnsuredAssigned2 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
42SELECT @EnsuredAssigned2
43
44-- Case 7: ROWCOUNT checked and default value assigned
45DECLARE @EnsuredAssigned3 CHAR(10)
46SELECT @EnsuredAssigned3 = Supplier FROM MDID_TRAN.dbo.TrOrderPo WHERE PoNum = '10101234'
47
48IF @@ROWCOUNT = 0
49BEGIN
50     -- Variable is assigned after @@ROWCOUNT check
51    SELECT @EnsuredAssigned3 = 'DEFAULT'
52END
53SELECT @EnsuredAssigned3
54
55-- Case 8: Variable is checked for NULL and assigned
56DECLARE @Supplier4 CHAR(5);
57SELECT @Supplier4 = Supplier 
58FROM MDID_TRAN.dbo.TrOrderPO
59WHERE PoNum = '10101234';
60
61IF ((@Supplier4 IS NULL))
62BEGIN
63        SET @Supplier4 = '';
64END;

Analysis Results

 MessageLineColumn
1SA0115 : Variable @output assignment from SELECT with no rows not ensured.77
2SA0115 : Variable @NotEnsuredAssigned assignment from SELECT with no rows not ensured.317
See Also

Other Resources