Stored Procedures vs Functions in SQL Server: When to Choose Which

These get confused constantly, including in interviews. Here’s the clean distinction, grounded in everything you’ve built across Chapters 2 and 4 rather than as a fresh set of rules to memorize.

Side by Side

The One-Sentence Rule

Functions’ composability inside SELECT statements is their key advantage — but only for read-only, single-result-shape logic. The moment you need to write data or control a transaction explicitly, you’re in stored procedure territory, no exceptions. This isn’t a style preference; it’s enforced by the engine, as Chapter 2’s “side-effecting operator” error demonstrated directly.

A Realistic Mixed Scenario

Real applications typically need both, working together: an iTVF to expose a reusable, JOIN-friendly “active customers this quarter” query, and a stored procedure that uses that same iTVF internally as part of a larger workflow that also writes an audit log row and sends the result back to the caller.

CREATE PROCEDURE dbo.usp_GenerateQuarterlyReport @quarter INT AS
BEGIN
    SET NOCOUNT ON;
    -- Reuses an iTVF from earlier in the chapter for the read-only part
    SELECT * FROM dbo.GetEmployeesByDepartment('Sales');
    -- Then does something only a procedure can: write an audit trail
    INSERT INTO dbo.ReportLog (report_name, generated_at) VALUES ('Quarterly Sales', SYSDATETIME());
END;

This is the natural end state once you’ve internalized both tools: functions for the composable, read-only building blocks; procedures for orchestrating them alongside anything with a side effect.

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;