SQL Server

Stored Procedures in SQL Server: A Beginner Introduction

Learn how to create, execute, parameterize, and alter a basic SQL Server stored procedure, and how it differs from a view.

By JaviPublished 12 min readBeginner

A stored procedure is a named database object that contains one or more SQL statements. An application or person can execute it by name and provide input values, rather than sending a newly assembled query every time. Procedures are commonly used to offer a consistent operation, keep repeated SQL in one place, and control which database actions an application account can perform.

This lesson creates dbo.usp_GetOrdersByCustomer, a procedure that returns orders for one customer from the synthetic JaviBuildsLab database. It builds on SELECT, WHERE and the reusable query idea in views. A procedure is not automatically faster, safer, or better than a well-designed query; its value depends on how it is used and maintained.

Create a simple stored procedure

In SSMS, connect to the intended database and create this procedure:

SQL query: Create a simple stored procedureT-SQL
CREATE PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, OrderDate, Status, Amount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC, OrderId DESC;
END;
T-SQL11 linesRead-only query display. It is not connected to a database.

The schema dbo is part of the procedure’s name. The usp_ prefix is a naming convention some teams use for user-created stored procedures; it is not required. @CustomerId int declares an input parameter named @CustomerId whose value must be an integer. The query then uses that value in its filter. SET NOCOUNT ON suppresses the extra “N rows affected” message for statements inside the procedure, which can make the output easier for clients to handle.

The body is between BEGIN and END. This example has one SELECT statement, but a procedure can contain multiple statements. Keep beginner procedures focused so that it is clear what input they accept and what result or action they provide. The ORDER BY defines the order returned by this SELECT; it is not a general property of the procedure’s data.

In SSMS, run the CREATE statement in the JaviBuildsLab database. A CREATE PROCEDURE statement must be the first statement in its query batch. If other statements precede it in the same script, place GO between batches or create the procedure in a separate query window. After running it, expand Programmability, then Stored Procedures in Object Explorer and refresh the node to see dbo.usp_GetOrdersByCustomer.

Execute the procedure

Call the procedure with EXEC (or the longer keyword EXECUTE) and supply a customer ID:

SQL query: Execute the procedureT-SQL
EXEC dbo.usp_GetOrdersByCustomer @CustomerId = 11;
T-SQL1 lineRead-only query display. It is not connected to a database.

The named form makes it clear which parameter receives the value. SQL Server runs the SELECT using 11 as @CustomerId and returns that customer’s orders. You can try another customer by changing the value. A customer with no matching orders returns an empty result set; the procedure has not failed merely because no rows were found.

Parameters let the caller reuse the same procedure with different inputs. The SQL statement stays fixed while the value changes. This is easier to review than constructing a new statement by joining strings, and it avoids treating a user-provided value as SQL code. Application code should still use its database driver’s parameter binding rather than inserting values into SQL text. This lesson stays with a simple integer parameter; advanced dynamic SQL is outside its scope.

If a parameter is not supplied and no default is defined, SQL Server reports that the required parameter is missing. A procedure can define defaults, but only add one when it has a clear business meaning. Do not choose a default simply to make an error disappear: an unintended value may return the wrong data or broaden an operation.

Choose a parameter type that matches the column and the meaning of the input. In this example, both @CustomerId and Customers.CustomerId are integers. Matching types makes the contract easy to understand and helps avoid implicit conversions that may complicate a query. Parameter names should describe the value, not its position. When a procedure has several inputs, named arguments make a call easier to review and help protect callers from mistakes if the procedure’s parameter order later changes.

An input value is data, not a replacement for an object name or a fragment of SQL syntax. A parameter can stand for a value in a condition, but it cannot make a table name dynamically change. If an application needs variable object names, it requires a different design and additional safeguards; that is beyond this beginner example. For ordinary values, keep the statement fixed and pass the value through a parameter.

Read the result and test the input

Inspect the rows returned for CustomerId = 11. Compare them with a direct SELECT that uses the same filter. Test a customer ID with multiple orders, and if the sample has a customer without orders, test that too. Validate both the useful result and the empty-result behavior.

The procedure name communicates the operation, but it does not guarantee the result is correct. If the table changes, the procedure may need to change. If a new caller depends on the procedure, the parameter name, type, and result columns become part of the interface. Keep those choices deliberate and test the procedure after edits.

For this read-only example, executing the procedure does not change table rows. Other procedures can insert, update, or delete data. Read the body and confirm its effects before running an unfamiliar procedure, especially in a shared or production database. Follow your team’s review process for changes.

Change a procedure with ALTER PROCEDURE

Use ALTER PROCEDURE to replace the stored definition while keeping the object’s identity. For example, this version also returns the customer’s name by joining to dbo.Customers:

SQL query: Change a procedure with ALTER PROCEDURET-SQL
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT c.CustomerId, c.FirstName, c.LastName,
           o.OrderId, o.OrderDate, o.Status, o.Amount
    FROM dbo.Customers AS c
    INNER JOIN dbo.Orders AS o
        ON o.CustomerId = c.CustomerId
    WHERE c.CustomerId = @CustomerId
    ORDER BY o.OrderDate DESC, o.OrderId DESC;
END;
T-SQL14 linesRead-only query display. It is not connected to a database.

Run the complete ALTER definition, then execute the procedure again and compare its columns and rows with what callers expect. A change to the result shape can affect applications, reports, and other procedures. Treat parameter names and types as an interface too; coordinate changes if existing callers rely on them.

Procedure versus view

A view is a named query that callers can read with SELECT. It is often useful when several consumers need a shared rowset, such as the customer-order result in dbo.vw_CustomerOrders. A stored procedure is executed with EXEC and can accept parameters; it can return a result set and may also perform other operations. A view is used as a query source, while a procedure is called as an operation.

Choose a view when the main goal is to expose a reusable query that can be filtered and joined by callers. Choose a procedure when callers need a named action with defined inputs or a sequence of statements. Both objects can help centralize SQL, and neither should hide unclear logic from its maintainers. See Views in SQL Server for the previous lesson’s example.

Permissions at a beginner level

Database permissions determine who may execute a procedure and who may read or change the underlying tables. An administrator can grant EXECUTE permission to an application user or role. The exact permission design depends on ownership, schemas, deployment, and organizational policy; have a database owner review it.

In an application, use a dedicated account or role with only the access it needs. Permission behavior across procedures and underlying tables can depend on ownership and execution context, so do not infer access from the procedure name or assume every deployment behaves the same way. A database owner should review the actual object ownership, grants, and application identity. These are design decisions that should be tested in the target environment, not improvised by granting broad rights to an example account.

Do not assume that putting SQL inside a procedure automatically makes it secure. The procedure must be designed appropriately, and permissions must be configured. Avoid granting broad permissions such as database ownership merely to make an example work. For learning, execute the procedure using your local practice account in JaviBuildsLab.

Performance and maintenance expectations

A stored procedure is not automatically faster than an equivalent query. It can improve reuse and consistency, but SQL Server still has to execute the underlying statements against the available data. Performance depends on the query, schema, indexes, data volume, and workload. Start by making the logic correct and understandable; measure real performance only when there is a real need.

Procedures are also not a replacement for good query design. A confusing join, missing filter, or unnecessary columns remain problems when wrapped in a procedure. Give parameters useful types, return only columns the caller needs, and document any assumptions that may surprise the next maintainer. Avoid adding procedural layers without a clear reason.

Practice and next step

Create the procedure in the practice database, execute it with customer 11, and compare the output with a direct SELECT. Alter it to include customer names, run it again, and note which parts of the result changed. Before using a procedure written by someone else, inspect its definition and identify whether it reads or changes data.

  1. Caller supplies @CustomerId
  2. EXEC stored procedure
  3. SELECT returns matching orders
Call a parameterized procedure

Procedures package a named operation. The next lesson looks at the keys that connect customer rows to order rows and help SQL Server protect those relationships: primary keys and foreign keys. Continue through the SQL Server Foundations roadmap whenever you want to review the complete sequence.

Check what you learned

2 quick questions

Question 1 of 2What is a stored procedure?
Question 2 of 2How can a procedure receive input?

SQL Server Foundations

Lesson 22 of 25

Continue to next lesson

Primary Keys and Foreign Keys in SQL Server

Continue to Lesson 23

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL