How to Create Stored Procedures in SQL Server

How do I create and use stored procedures in SQL Server?

A stored procedure is a precompiled T-SQL program that you save in the database and execute on demand.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is a stored procedure in SQL Server?

A stored procedure is a named block of T-SQL statements stored in the database. Because it is precompiled, it runs faster than sending ad-hoc SQL and centralizes business logic for reuse and security.

How do you create a stored procedure?

Use CREATE PROCEDURE followed by the procedure name, parameters, and the body wrapped in AS ... BEGIN ... END. Always qualify the name with its schema (dbo by default) to avoid ambiguity.

Basic CREATE PROCEDURE syntax

CREATE PROCEDURE dbo.GetCustomerOrders @CustomerId INT AS BEGIN ... END creates a reusable query that returns orders for one customer.

How do you execute a stored procedure?

Run EXEC dbo.GetCustomerOrders @CustomerId = 42; or the shorter EXEC GetCustomerOrders 42;. You can also use sp_executesql when you need dynamic SQL inside the procedure.

How do you alter or drop a stored procedure?

Use ALTER PROCEDURE to change the definition without losing permissions. Use DROP PROCEDURE dbo.GetCustomerOrders; to remove it completely.

When should you use stored procedures?

Choose procedures when you need to encapsulate complex joins, ensure consistent filters, improve performance with plan caching, or restrict direct table access for security.

What are best practices for stored procedures?

1) Always set SET NOCOUNT ON; to avoid extra result sets.
2) Validate parameters at the top.
3) Return data via SELECT or output parameters, not PRINT.
4) Keep procedures focused on one task for easier maintenance.

Full example: get customer order summary

The sample procedure below joins Customers, Orders, and OrderItems to return total spend per customer within a date range.

Why How to Create Stored Procedures in SQL Server is important

How to Create Stored Procedures in SQL Server Example Usage


-- Create the procedure
CREATE OR ALTER PROCEDURE dbo.GetTopCustomers
    @MinTotal DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  c.id,
            c.name,
            SUM(o.total_amount) AS lifetime_value
    FROM    Customers c
    JOIN    Orders    o ON o.customer_id = c.id
    GROUP BY c.id,c.name
    HAVING SUM(o.total_amount) >= @MinTotal
    ORDER BY lifetime_value DESC;
END;

-- Execute it to find customers who spent at least $10,000
EXEC dbo.GetTopCustomers @MinTotal = 10000;

How to Create Stored Procedures in SQL Server Syntax


CREATE OR ALTER PROCEDURE dbo.GetCustomerOrderSummary
    @CustomerId   INT,
    @StartDate    DATETIME = '2000-01-01',
    @EndDate      DATETIME = '9999-12-31'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  c.id          AS customer_id,
            c.name        AS customer_name,
            o.id          AS order_id,
            o.order_date,
            SUM(oi.quantity * p.price) AS order_total
    FROM    Customers  c
    JOIN    Orders     o  ON o.customer_id = c.id
    JOIN    OrderItems oi ON oi.order_id   = o.id
    JOIN    Products   p  ON p.id          = oi.product_id
    WHERE   c.id = @CustomerId
      AND   o.order_date BETWEEN @StartDate AND @EndDate
    GROUP BY c.id,c.name,o.id,o.order_date;
END

Common Mistakes

Omitting the schema name (e.g., using CREATE PROCEDURE GetData). SQL Server then creates it in the user’s default schema, causing ‘procedure not found’ errors for others. Always prefix with dbo.
Forgetting SET NOCOUNT ON inside the procedure. Extra result sets return to the client, breaking ORM mapping. Add SET NOCOUNT ON as the first statement.

Frequently Asked Questions (FAQs)

Can a stored procedure return a value?

Yes. Use RETURN int_value for status codes or define OUTPUT parameters to send data back to the caller.

How do I debug a stored procedure?

Use SQL Server Management Studio’s debugger or insert temporary logging with PRINT and table variables. For performance tuning, capture execution plans with SET STATISTICS PROFILE ON.

Are stored procedures faster than parameterized queries?

Often yes, because the execution plan is cached once and reused. However, well-written parameterized queries can achieve similar performance; choose procedures for encapsulation and security benefits.