LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 3119 min read

Stored Procedures

Learn how to create and execute stored procedures in SQL Server, pass parameters, and why procedures help centralize business logic.

Introduction

A stored procedure is a named, precompiled batch of T-SQL stored inside the database itself. Instead of an application sending raw SQL every time it needs to insert an order or run a report, it can simply call a procedure by name and pass in a few parameters. This lesson covers how to create, parameterize, and execute stored procedures, and why they are a cornerstone of well-organized SQL Server applications.

What You Will Learn
  • Why stored procedures centralize and reuse business logic.
  • How to create a procedure with CREATE PROCEDURE.
  • How to run a procedure with EXEC.
  • How to declare input and output parameters.
  • How to give parameters default values.

Why Stored Procedures?

Stored procedures give you a single, controlled place for logic that would otherwise be duplicated across applications, reports, and scripts. Because the procedure lives in the database, every caller gets the same behavior, the same validation, and the same execution plan caching, without needing to know the underlying table structure.

BenefitExplanation
ReusabilityOne definition, called from many applications or scripts
PerformanceExecution plans are compiled once and reused for later calls
SecurityUsers can be granted EXECUTE rights without direct table access
MaintainabilityLogic changes happen in one place instead of scattered SQL strings

Creating a Procedure

CREATE PROCEDURE (often abbreviated CREATE PROC) is followed by a name, an optional parameter list, and an AS block containing the T-SQL to run.

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;


    SELECT
        o.OrderID,
        o.OrderDate,
        o.Status
    FROM dbo.Orders o
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC;
END;
GO
SET NOCOUNT ON

SET NOCOUNT ON suppresses the '(N rows affected)' message SQL Server sends after each statement. It is standard practice in procedures because it reduces network chatter and avoids confusing client applications that parse result sets.

Executing a Procedure

Use EXEC (or the full keyword EXECUTE) to run a procedure, passing parameter values either positionally or, more clearly, by name.

EXEC dbo.GetCustomerOrders @CustomerID = 104;
Result

Click Run to see what this code prints.

Input Parameters

A procedure can take multiple input parameters, each with its own data type. They behave like local variables scoped to the procedure.

CREATE PROCEDURE dbo.AddCustomer
    @FirstName VARCHAR(50),
    @LastName  VARCHAR(50),
    @Email     VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;


    INSERT INTO dbo.Customers (FirstName, LastName, Email, IsActive)
    VALUES (@FirstName, @LastName, @Email, 1);
END;
GO


EXEC dbo.AddCustomer
    @FirstName = 'Dana',
    @LastName  = 'Wu',
    @Email     = 'dana.wu@example.com';

Output Parameters

An OUTPUT parameter lets a procedure return a value back to the caller through the parameter itself, which is useful for things like a newly generated ID or a computed total.

CREATE PROCEDURE dbo.AddCustomerAndGetId
    @FirstName VARCHAR(50),
    @LastName  VARCHAR(50),
    @NewCustomerID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;


    INSERT INTO dbo.Customers (FirstName, LastName, IsActive)
    VALUES (@FirstName, @LastName, 1);


    SET @NewCustomerID = SCOPE_IDENTITY();
END;
GO


DECLARE @NewId INT;
EXEC dbo.AddCustomerAndGetId
    @FirstName = 'Omar',
    @LastName  = 'Farouk',
    @NewCustomerID = @NewId OUTPUT;


SELECT @NewId AS NewCustomerID;
Result

Click Run to see what this code prints.

Default Parameter Values

You can give a parameter a default value, which lets callers omit it entirely when the default is fine.

CREATE PROCEDURE dbo.GetOrdersByStatus
    @Status VARCHAR(20) = 'Shipped'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, CustomerID, OrderDate, Status
    FROM dbo.Orders
    WHERE Status = @Status;
END;
GO


EXEC dbo.GetOrdersByStatus;                 -- uses the default 'Shipped'
EXEC dbo.GetOrdersByStatus @Status = 'Pending';

Common Mistakes

Avoid These Mistakes
  • Concatenating parameter values directly into dynamic SQL strings, opening the door to SQL injection.
  • Forgetting SET NOCOUNT ON in procedures that run inside loops or triggers, which adds unnecessary overhead.
  • Not validating parameters (like a negative CustomerID) before using them in a query.
  • Making one giant procedure that does everything, instead of smaller, focused, composable procedures.
  • Relying on @@IDENTITY instead of SCOPE_IDENTITY(), which can return an ID from an unrelated trigger-fired insert.

Best Practices

  • Always call parameters by name (@CustomerID = 104) rather than relying on position.
  • Use SET NOCOUNT ON at the start of every procedure.
  • Validate and sanity-check input parameters before using them.
  • Keep procedures focused on a single responsibility.
  • Grant EXECUTE permission on procedures instead of direct table access where possible.

Frequently Asked Questions

A procedure can perform actions like INSERT/UPDATE/DELETE and does not have to return a value, while a function must return a value (scalar or table) and generally cannot modify data. The next lesson covers functions in detail.

Yes, procedures can call other procedures, which is a common way to compose smaller, reusable units of logic.

They can, since SQL Server caches and reuses their execution plan across calls, but the bigger benefit is usually maintainability, security, and reduced network traffic compared to sending raw SQL from the application.

Yes - a procedure can contain multiple SELECT statements, and the caller (or client library) reads them as separate result sets in order.

Key Takeaways

  • Stored procedures package T-SQL logic under a name that applications call with EXEC.
  • Parameters can be input, output, or given default values.
  • Procedures centralize logic, improve security, and reduce duplicated SQL.
  • SCOPE_IDENTITY() is the safe way to retrieve a just-inserted identity value.
  • SET NOCOUNT ON is standard practice inside procedures.

Summary

Stored procedures let you move logic into the database server itself, where it can be reused, secured, and optimized in one place. Next, you will look at user-defined functions, which complement procedures by letting you package reusable calculations and reusable table shapes directly into your queries.

Next Lesson →

User-Defined Functions