What a Stored Procedure Is
A stored procedure is a named, precompiled batch of T-SQL statements stored in the database and invoked with EXEC or EXECUTE, optionally passing input parameters and receiving output parameters or a return code. Because the procedure lives on the server, calling it sends only its name and parameter values across the network instead of the full SQL text, reducing network traffic and letting the query optimizer reuse cached execution plans across calls.
Cricket analogy: CREATE PROCEDURE usp_BowlOver is like a bowling coach codifying a set line-and-length routine that any bowler on the squad can invoke by name before every over, instead of re-explaining the plan each time.
Parameters and Return Values
Procedures accept input parameters declared after the procedure name (with optional defaults), and can expose OUTPUT parameters that pass a value back to the caller, distinct from a result set returned by SELECT. A procedure can also return a single integer status code via RETURN, conventionally used for success/failure codes (0 typically meaning success) rather than business data, since RETURN can only carry one integer.
Cricket analogy: A bowling procedure taking @lineType VARCHAR(10) = 'outswing' as a default parameter mirrors a captain who defaults to an outswing field unless a different line is explicitly called for a specific batter.
CREATE OR ALTER PROCEDURE dbo.usp_TransferFunds
@FromAccountId INT,
@ToAccountId INT,
@Amount MONEY,
@NewBalance MONEY OUTPUT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromAccountId;
UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToAccountId;
SELECT @NewBalance = Balance FROM dbo.Accounts WHERE AccountId = @FromAccountId;
RETURN 0;
END;
GO
DECLARE @resultBalance MONEY, @rc INT;
EXEC @rc = dbo.usp_TransferFunds
@FromAccountId = 101,
@ToAccountId = 202,
@Amount = 250.00,
@NewBalance = @resultBalance OUTPUT;Plan Caching and Recompilation
SQL Server caches an execution plan for a procedure the first time it runs and reuses that plan on subsequent calls, which is faster but can cause parameter sniffing — a plan optimized for the first parameter values gets reused for very different values, sometimes performing poorly. WITH RECOMPILE on the procedure or a specific statement forces a fresh plan to be generated, trading compilation overhead for a plan tailored to the current parameters, which is a targeted fix rather than something to apply blindly to every procedure.
Cricket analogy: Parameter sniffing is like a bowling coach setting a field plan against a specific tailender's weaknesses, then leaving that same field in place when a power-hitter like Andre Russell comes to the crease.
Use sp_recompile 'dbo.usp_TransferFunds' to force plan recompilation on next execution without altering the procedure, or add OPTION (RECOMPILE) to an individual statement inside the procedure to recompile just that statement each run — a lighter-weight fix than WITH RECOMPILE on the whole procedure.
Always include SET NOCOUNT ON near the top of a procedure that does INSERT/UPDATE/DELETE. Without it, SQL Server sends a 'N rows affected' message after every statement, which adds network overhead and can confuse client applications that misinterpret the extra result.
- Stored procedures are precompiled, named T-SQL batches invoked with EXEC, reducing network traffic versus sending raw SQL text.
- Parameters can have defaults; OUTPUT parameters return values to the caller distinct from a SELECT result set.
- RETURN provides a single integer status code, conventionally used for success/failure, not business data.
- SQL Server caches execution plans per procedure; parameter sniffing occurs when a cached plan mismatches new parameter values.
- WITH RECOMPILE or OPTION (RECOMPILE) forces plan regeneration, trading compile time for a plan tuned to current inputs.
- SET NOCOUNT ON suppresses row-count messages and should be a near-default habit in procedures with DML statements.
- CREATE OR ALTER PROCEDURE lets you redeploy a procedure definition idempotently without a separate DROP step.
Practice what you learned
1. What is the primary mechanism used to call a stored procedure?
2. What is the key difference between an OUTPUT parameter and RETURN?
3. What problem does parameter sniffing describe?
4. What does SET NOCOUNT ON do?
5. Which statement forces a stored procedure to generate a fresh execution plan on its next call without altering its definition?
Was this page helpful?
You May Also Like
Variables and Control Flow
Learn how T-SQL variables store scalar values inside a batch and how IF/ELSE and WHILE let you branch and loop through procedural logic.
User-Defined Functions
Learn the differences between scalar, inline table-valued, and multi-statement table-valued functions in T-SQL, and when each is the right tool.
Error Handling with TRY/CATCH
Learn how TRY...CATCH blocks intercept T-SQL runtime errors, how to inspect them with the ERROR_ functions, and how to combine them safely with transactions.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics