Learn T-SQL Querying

Avoiding unnecessary overhead with stored procedures

In stored procedures, use the SET NOCOUNT ON notation even when there's a requirement to return current row count during execution, like in the following example:

CREATE OR ALTER PROCEDURE [dbo].[uspStocksPerWorkOrder] @WorkOrderID [int]ASBEGINSET NOCOUNT ON;       SELECT wo.StockedQty, wor.WorkOrderID       FROM Production.WorkOrder AS wo       LEFT JOIN Production.WorkOrderRouting AS wor ON wo.WorkOrderID = wor.WorkOrderID       WHERE wo.WorkOrderID = @WorkOrderID;END;

When SET NOCOUNT is ON, the count indicating the number of rows affected by a T-SQL statement is not returned to the application layer, which provides a performance boost.

The @@ROWCOUNT function will still be incremented even with SET NOCOUNT ...