T-SQL Plugin - new rule for stored procedures

Clean Code New rules / language support
1

It’s a rule for the code smells section:

The non-compliant code is like this:

CREATE PROCEDURE dbo.Test
AS 
BEGIN

SELECT 1

END

The compliant code should looks like:

CREATE PROCEDURE dbo.Test
AS 
BEGIN

SET NOCOUNT ON; 

SELECT 1

END
3

Hello Chris,

You are suggesting a rule to force developers to configure the NOCOUNT statement to “ON” to prevent the SQL Server to return the number of rows affected by the procedure.

Can you explain why this is a problem to have this rows count returned as part of the result set? What is the impact if NOCOUNT is set to OFF?

Thanks

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-2017

4

Hi Alexandre

alone SET NOCOUNT ON is not interesting, but in stored procedure or functions it gets interesting, it increases the performance and avoid the return of not expected results(-sets).

See the following articles:
https://www.mssqltips.com/sqlservertip/1226/set-nocount-on-improves-sql-server-stored-procedure-performance/ ;
https://www.red-gate.com/hub/product-learning/sql-prompt/finding-code-smells-using-sql-prompt-set-nocount-problem-pe008-pe009

Thank you.

Christian

Christian Rohr

Site Reliability Engineer

t +49 (0) 6221 7961-105
m +49 (0) 1520 927 59 17

christian.rohr@verivox.com

1 Like
5

Hello Chris,

Here is related specification RSPEC-4819 and implementation ticket SONARTSQL-143.

Regards

2 Likes