# Calling a Stored Procedure from C#

In ADO.NET, put the procedure's name in a SqlCommand, set its CommandType to StoredProcedure, and add each parameter by name. Then read the results as from any query.

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

## After this lesson you can

- Call a stored procedure from C# with SqlCommand, CommandType.StoredProcedure and parameters

## Key takeaways

- To call a procedure, put its name in the `SqlCommand` and set `CommandType` to `CommandType.StoredProcedure`.
- Add each parameter by its name, `@` included: `command.Parameters.AddWithValue("@CategoryName", "Produce")`.
- Read the rows a procedure returns with `ExecuteReader`, and a single value with `ExecuteScalar`, as with any query.
- Leave out a parameter that has a default, and the procedure uses the default.
- Without `CommandType.StoredProcedure`, the procedure runs with no parameters, and SQL Server says one was not supplied.

Calling a stored procedure from C# is almost the same as running a query with ADO.NET, from *Connecting Apps to SQL Server*. Three things change:

1. The command's text is the **procedure's name**, not a `SELECT`.
2. The command's **`CommandType`** is `CommandType.StoredProcedure`.
3. Each parameter is added by its **name**, `@` included, as in the procedure.

## Reading rows

This program calls `GetProductsByCategory`, from the last lesson, and prints what it returns:

```csharp
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("GetProductsByCategory", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CategoryName", "Produce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    string name = (string)reader["ProductName"];
    decimal price = (decimal)reader["UnitPrice"];
    Console.WriteLine($"{name}: {price:0.00}");
}
```

```text
Longlife Tofu: 10.00
Manjimup Dried Apples: 53.00
Rössle Sauerkraut: 45.60
Tofu: 23.25
Uncle Bob's Organic Dried Pears: 30.00
```

`CommandType` lives in the `System.Data` namespace, hence the extra `using`. The reader reads the procedure's columns by name, exactly as it would a query's.

## Reading one value

`CountProductsInCategory` returns a single value, so call it with `ExecuteScalar`:

```csharp
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

foreach (string category in new[] { "Seafood", "Produce", "Beverages" })
{
    using SqlCommand command = new SqlCommand("CountProductsInCategory", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CategoryName", category);

    int count = (int)command.ExecuteScalar();
    Console.WriteLine($"{category}: {count} products");
}
```

```text
Seafood: 12 products
Produce: 5 products
Beverages: 12 products
```

## Leaving out a parameter with a default

`SearchProducts` has a default for `@MaxPrice`. Leave that parameter out in C#, and the default is used, just as in `EXEC`:

```csharp
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("SearchProducts", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Text", "Sauce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["ProductName"]);
}
```

```text
Northwoods Cranberry Sauce
Louisiana Fiery Hot Pepper Sauce
```

## Common problems

| What you see | What to do |
|---|---|
| `Could not find stored procedure 'GetProductsByCategory'` | The procedure doesn't exist in the database you connected to. Create it in SSMS, in the right database. |
| `Procedure or function 'GetProductsByCategory' expects parameter '@CategoryName', which was not supplied` | A parameter without a default is missing or misspelled, or `CommandType` wasn't set to `StoredProcedure`: without it, the procedure runs with no parameters at all. |

## Check yourself

1 question to try on the lesson page.

## So what

A procedure's name, `CommandType.StoredProcedure` and named parameters: that's all it takes for your C# to use logic stored in the database. The DAOs you write later can call procedures and queries side by side.

## Key terms

- [Stored Procedure](https://www.ask-codey.com/glossary/stored-procedure)
- [CommandType](https://www.ask-codey.com/glossary/commandtype)

## Cheat sheets

- [Calling a Stored Procedure from C#](https://www.ask-codey.com/cheat-sheets/stored-procedures-in-t-sql-calling-a-stored-procedure-from-csharp-cheat-sheet)

Source: https://www.ask-codey.com/lessons/stored-procedures-in-t-sql-calling-a-stored-procedure-from-csharp