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