Creating Stored Procedures in SQL Server: SET NOCOUNT ON and the Basics

Chapter 2’s function types kept hitting the same wall: none of them can modify data or manage a transaction. A stored procedure is precompiled, reusable, parameterized T-SQL logic that removes that wall entirely — it can perform INSERT/UPDATE/DELETE, manage transactions, use full TRY/CATCH, return multiple result sets, and doesn’t have to return anything at all. This is where the procedural half of T-SQL really begins.

Your First Procedure

CREATE PROCEDURE dbo.usp_GetCustomersByTier
    @tier NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON; -- near-universal best practice, see below
    SELECT customer_id, name, email FROM dbo.Customer WHERE tier = @tier;
END;
GO

EXEC dbo.usp_GetCustomersByTier @tier = 'premium';
-- Equivalent, positional call — works but is fragile if parameter order ever changes:
EXEC dbo.usp_GetCustomersByTier 'premium';

The usp_ prefix is a long-standing naming convention (“user stored procedure”) — avoid the older sp_ prefix specifically, since SQL Server always checks the system master database first for anything named sp_*, adding a small but real, entirely avoidable lookup cost to every call.

Why SET NOCOUNT ON Matters More Than It Looks

Without SET NOCOUNT ON, this extra network chatter can measurably slow down procedures that loop or run many statements, and can actively interfere with some client libraries and reporting tools that misinterpret the extra “rows affected” messages as additional result sets. Put it at the top of every procedure by default — there is essentially never a reason not to.

A Procedure Precompiles — What That Actually Means

Unlike an ad-hoc query sent fresh from an application each time, a stored procedure’s execution plan is compiled once (on first call, or after certain invalidating events like a statistics update) and reused on subsequent calls. This is a real, measurable performance advantage for frequently-run logic — but it’s also the exact mechanism behind parameter sniffing, a real gotcha covered fully once you reach the Performance Tuning course: the plan compiled for the first parameter value seen gets reused for every subsequent call, even ones with very differently-shaped data.

ALTER, DROP, and Modifying Procedures Safely

-- Change the body without dropping and losing permissions granted on it
ALTER PROCEDURE dbo.usp_GetCustomersByTier
    @tier NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT customer_id, name, email, tier FROM dbo.Customer WHERE tier = @tier; -- added tier column
END;
GO

DROP PROCEDURE IF EXISTS dbo.usp_GetCustomersByTier;