What is a stored procedure and when would you use one?
Quick Answer
A stored procedure is a named, precompiled batch of T-SQL saved in the database that you call with EXEC, optionally passing parameters. It centralises data logic, is parameterised (so it resists SQL injection), can return result sets and output parameters, and its execution plan is cached and reused.
A stored procedure is a saved, named block of T-SQL in the database that you call with EXEC and pass parameters to. The benefits are that it keeps complex data logic in one place, it's always parameterised so it's safe against injection, you can grant execute rights without exposing the tables, and the plan gets cached and reused. The difference from a function is that a function is side-effect-free and used inside a query, whereas a procedure can modify data and manage its own transaction. These days a lot of teams keep the queries in the application with EF Core or Dapper for testability and version control, and use procedures mainly for big set-based batch work — so I'd mention both approaches.
Why teams use them:
- Encapsulation — complex multi-statement logic lives in one place; the app calls
EXEC usp_PlaceOrder. - Security — grant
EXECUTEon the procedure without granting direct table access; always parameterised. - Performance — plan reuse; less network traffic than sending a large batch.
- Maintainability — change the query without redeploying the app (double-edged: logic hidden from source control if not managed).
Stored procedure vs function: a function must be deterministic-friendly, can't have side effects, and is used inside a query (SELECT dbo.fn_Tax(...)); a procedure can modify data, do transactions and error handling, and is called, not embedded.
The modern debate: many teams now keep query logic in the application (with EF Core or Dapper) for testability and source control, and reserve procedures for heavy set-based operations, batch jobs, or where a DBA owns the data tier. Interviewers usually want you to articulate both sides.