# Creating Stored Procedures

A stored procedure is a named, saved batch of SQL that takes parameters. Create one with CREATE OR ALTER PROCEDURE, give parameters defaults, and run it with EXEC.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## After this lesson you can

- Create a stored procedure with parameters, and run it with EXEC
- Give a parameter a default value, and pass parameters by name

## Key takeaways

- A stored procedure is SQL saved in the database under a name, and it takes parameters, like a C# method. Create one with `CREATE OR ALTER PROCEDURE`.
- Each parameter starts with `@` and has a type: use the same type as the column it's compared with.
- Run a procedure with `EXEC`, passing values by name, such as `@CategoryName = 'Produce'`: clearer than by order.
- A parameter with a default, such as `@MaxPrice money = 1000`, can be left out, and the default is used.
- Parameters keep values separate from the SQL, so they're safe from SQL injection.

A view saves a query, but it can't take parameters. A **stored procedure** can: it's a named piece of SQL, saved in the database, that takes values each time it runs, like a method in C#.

## Creating one

```sql
CREATE OR ALTER PROCEDURE GetProductsByCategory
    @CategoryName nvarchar(15)
AS
BEGIN
    SELECT p.ProductId, p.ProductName, p.UnitPrice
    FROM Products AS p
    INNER JOIN Categories AS c
        ON p.CategoryId = c.CategoryId
    WHERE c.CategoryName = @CategoryName
    ORDER BY p.ProductName;
END;
```

- **`CREATE OR ALTER PROCEDURE`**, then the name. (`CREATE PROC` is a common short form.)
- **Parameters** come after the name: each starts with `@` and has a type, like a C# parameter. Use the same type as the column it's compared with: `CategoryName` is `nvarchar(15)`.
- The body, between **`BEGIN`** and **`END`**, can hold any SQL, many statements if you need them.
- Like a view, it must be the only statement in its batch: use `GO` around it in a longer script.

In SSMS, the procedure appears under **Programmability > Stored Procedures**.

## Running it: EXEC

```sql
EXEC GetProductsByCategory @CategoryName = 'Produce';
```

| ProductId | ProductName | UnitPrice |
|---|---|---|
| 74 | Longlife Tofu | 10.00 |
| 51 | Manjimup Dried Apples | 53.00 |
| 28 | Rössle Sauerkraut | 45.60 |
| 14 | Tofu | 23.25 |
| 7 | Uncle Bob's Organic Dried Pears | 30.00 |

Values can also be passed in order, without names: `EXEC GetProductsByCategory 'Produce';`. Naming them is clearer, and safer when a procedure has several.

A procedure can return a single value too:

```sql
CREATE OR ALTER PROCEDURE CountProductsInCategory
    @CategoryName nvarchar(15)
AS
BEGIN
    SELECT COUNT(*)
    FROM Products AS p
    INNER JOIN Categories AS c
        ON p.CategoryId = c.CategoryId
    WHERE c.CategoryName = @CategoryName;
END;
```

```sql
EXEC CountProductsInCategory 'Seafood';
```

It returns one row with one value: 12.

## Several parameters, and defaults

A parameter can have a **default**, used when the caller leaves it out:

```sql
CREATE OR ALTER PROCEDURE SearchProducts
    @Text nvarchar(40)
    , @MaxPrice money = 1000
AS
BEGIN
    SELECT ProductName, UnitPrice
    FROM Products
    WHERE ProductName LIKE '%' + @Text + '%'
        AND UnitPrice <= @MaxPrice
    ORDER BY UnitPrice DESC;
END;
```

```sql
EXEC SearchProducts @Text = 'Sauce';
```

| ProductName | UnitPrice |
|---|---|
| Northwoods Cranberry Sauce | 40.00 |
| Louisiana Fiery Hot Pepper Sauce | 21.05 |

```sql
EXEC SearchProducts @Text = 'Sauce', @MaxPrice = 30;
```

| ProductName | UnitPrice |
|---|---|
| Louisiana Fiery Hot Pepper Sauce | 21.05 |

## Why use stored procedures

- **Logic lives in one place.** Every app that needs "products in a category" calls the same procedure.
- **Parameters keep values separate from SQL**, just as `SqlParameter` does in C#, so they're safe from SQL injection.
- **They can do more than one thing.** A procedure can check, insert and update in one call.

Teams differ on how much to put in procedures. In this course, most SQL lives in your C# data access code, and procedures are one more tool you can reach for.

## Check yourself

2 questions to try on the lesson page.

## So what

A stored procedure is SQL with a name and parameters, saved where every app can call it. Next, you'll call one from C#.

## Key terms

- [Stored Procedure](https://www.ask-codey.com/glossary/stored-procedure)
- EXEC
- BEGIN...END

## Cheat sheets

- [Creating Stored Procedures](https://www.ask-codey.com/cheat-sheets/stored-procedures-in-t-sql-creating-stored-procedures-cheat-sheet)

Source: https://www.ask-codey.com/lessons/stored-procedures-in-t-sql-creating-stored-procedures