Home

Description

The "NOCOUNT should be activated on PROCEDURE and TRIGGER definitions" rule states that the NOCOUNT option should be enabled for all stored procedures and triggers. This option prevents the query processor from returning the number of rows affected by each statement in a batch or stored procedure. This can help improve performance by reducing the amount of data sent over the network. It is recommended to enable this option for all stored procedures and triggers to ensure optimal performance.

Key Benefits

• Prevent Accidental Modifications: No count should be activated on PROCEDURE and TRIGGER definitions to ensure that no records are affected when the code is executed. This helps to prevent accidental modifications to data and ensures that the code is only used for its intended purpose.
• Accuracy: Activating No Count on PROCEDURE and TRIGGER definitions ensures that the code runs accurately and as expected, which helps to reduce the risk of errors and incorrect results.
• Security: Activating No Count on PROCEDURE and TRIGGER definitions helps to protect the data from malicious or unintended modifications, thus improving the overall security of the system.

Non-compliant Code Example

CREATE PROCEDURE [dbo].[GetAllProducts]  --Non compliant code (NOCOUNT is not activated on the procedure)
AS
BEGIN
    -- Insert statements for procedure here
    SELECT * From Production.Product;
END;
GO

Compliant Code Example

CREATE PROCEDURE [dbo].[GetAllProducts] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;   --Compliant code (NOCOUNT is activated on the procedure)

    -- Insert statements for procedure here
    SELECT * From Production.Product;
END;
GO