Home

Description

The "Output parameters should be assigned" rule states that any output parameters declared in a SQL Server stored procedure should be assigned a value before the procedure is executed. This ensures that the output parameters are not left unassigned, which can lead to unexpected behavior and errors. Additionally, this rule helps to make the code more readable and maintainable by making it clear which output parameters are being used and what values they should be assigned.

Key Benefits

• Ensures Quality: Assigning output parameters ensures that the values returned by a function are of the expected type and within the specified range.
• Simplifies Debugging: Assigning output parameters simplifies debugging by making it easier to trace the values returned by a function.
• Improves Performance: Assigning output parameters improves performance by avoiding unnecessary memory allocations.

Non-compliant Code Example

CREATE PROCEDURE GetImmediateManager  
   @employeeID INT,  
   @managerID INT OUTPUT   --Non compliant code (OUTPUT parameter is not assigned)
AS  
BEGIN  
   SELECT ManagerID
   FROM HumanResources.Employee
   WHERE EmployeeID = @employeeID  
END

Compliant Code Example

CREATE PROCEDURE GetImmediateManager  
   @employeeID INT,  
   @managerID INT OUTPUT  
AS  
BEGIN  
   SELECT @managerID = ManagerID  --Compliant code (OUTPUT parameter is assigned)
   FROM HumanResources.Employee
   WHERE EmployeeID = @employeeID  
END