ASP.NET Core Web API · Data

Stored Procedures

Many companies keep business logic in stored procedures written by their DBAs. The ShopSmart API must call two of them: one that lists products by category and one that adjusts stock and reports the new level. EF Core and ADO.NET both make this straightforward.

Intermediate 8 min Lesson 15 of 37Examples target .NET 9.

Before you start:ADO.NET Integration SQL Server: Stored Procedures

0/37 in ASP.NET Core Web API

On this page · 8 min · Intermediate

Interview questions

In simple words#

Stored procedures are pre-agreed recipes kept in the warehouse; the API just says “run recipe PlaceOrder with these values”.

CommandType.StoredProcedure: ADO.NET call.
FromSql: EF Core call for procedures returning rows.
Output parameters: Values sent back.

Learning objectives#

Query a stored procedure into DTOs with Database.SqlQuery
Execute a procedure with parameters and an OUTPUT parameter
Translate errors raised with THROW into API responses
Keep calls safe from SQL injection

Explanation#

For result sets, db.Database.SqlQuery<T>(

quot;EXEC dbo.usp_GetProductsByCategory @CategoryId = {id}") (EF Core 8+) maps columns to any class by name. The interpolated values become parameters — it is not string concatenation.

For entity types, db.Products.FromSql(

quot;EXEC …") works too, but you cannot compose further LINQ after a stored procedure call.

For commands, use db.Database.ExecuteSqlAsync(

quot;…") or ExecuteSqlRawAsync with SqlParameter objects when you need OUTPUT parameters (Direction = ParameterDirection.Output).

Procedures signal business errors with THROW 50001, 'message', 1. Catch SqlException, check Number, and return a 422 problem — other numbers are real failures (500).

With plain ADO.NET, set command.CommandType = CommandType.StoredProcedure and add parameters by name.

Syntax#

C# Illustrative

1var rows = await db.Database
2    .SqlQuery<ProductRow>(
quot;EXEC dbo.usp_GetProductsByCategory @CategoryId = {categoryId}")
3    .ToListAsync(ct);
4

5var newStock = new SqlParameter("@NewStock", SqlDbType.Int) { Direction = ParameterDirection.Output };
6await db.Database.ExecuteSqlRawAsync("EXEC dbo.usp_AdjustStock @ProductId, @Delta, @NewStock OUTPUT", [id, delta, newStock], ct);

Code example: Two stored procedures behind API endpoints#

C#Controllers/StockController.cs Illustrative

1// Program.cs
2using System.Net.Http.Json;
3using Microsoft.Data.SqlClient;
4using Microsoft.EntityFrameworkCore;
5

6const string Server = @"Server=(localdb)\mssqllocaldb;Trusted_Connection=True;TrustServerCertificate=True";
7var builder = WebApplication.CreateBuilder(args);
8builder.Logging.ClearProviders();                                    // keep the demo output clean
9builder.Services.AddControllers();
10builder.Services.AddDbContext<ShopDb>(o => o.UseSqlServer(Server + ";Database=ShopSmartApi_Procs"));
11var app = builder.Build();
12

13// Demo setup: the DBA's database, table and procedures
14await using (var setup = new SqlConnection(Server + ";Database=master"))
15{
16    await setup.OpenAsync();
17    await new SqlCommand("DROP DATABASE IF EXISTS ShopSmartApi_Procs; CREATE DATABASE ShopSmartApi_Procs;", setup).ExecuteNonQueryAsync();
18    setup.ChangeDatabase("ShopSmartApi_Procs");
19    foreach (var sql in new[]
20    {
21        """
22        CREATE TABLE Products (Id INT PRIMARY KEY, CategoryId INT NOT NULL, Name NVARCHAR(100) NOT NULL, Price DECIMAL(10,2) NOT NULL, Stock INT NOT NULL);
23        INSERT INTO Products VALUES (1, 1, 'Wireless Mouse', 799, 10), (2, 1, 'USB-C Hub', 1499, 5), (3, 2, 'Bluetooth Speaker', 3299, 3);
24        """,
25        """
26        CREATE PROCEDURE dbo.usp_GetProductsByCategory @CategoryId INT AS
27            SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId ORDER BY Price;
28        """,
29        """
30        CREATE PROCEDURE dbo.usp_AdjustStock @ProductId INT, @Delta INT, @NewStock INT OUTPUT AS
31        BEGIN
32            SET NOCOUNT ON;
33            UPDATE Products SET @NewStock = Stock = Stock + @Delta WHERE Id = @ProductId AND Stock + @Delta >= 0;
34            IF @@ROWCOUNT = 0 THROW 50001, 'Stock cannot go below zero (or the product does not exist).', 1;
35        END
36        """,
37    })
38        await new SqlCommand(sql, setup).ExecuteNonQueryAsync();
39}
40

41app.MapControllers();
42app.Urls.Add("http://127.0.0.1:5215");
43await app.StartAsync();
44

45using var http = new HttpClient { BaseAddress = new Uri("http://127.0.0.1:5215") };
46Console.WriteLine(
quot;Category 1: {await http.GetStringAsync("/api/categories/1/products")}");
47foreach (var delta in new[] { -3, 20, -50 })
48{
49    var response = await http.PostAsJsonAsync("/api/products/2/stock-adjustments", new { delta });
50    var body = await response.Content.ReadFromJsonAsync<System.Text.Json.JsonElement>();
51    var text = response.IsSuccessStatusCode ? body.ToString() : 
quot;problem: {body.GetProperty("title")}";
52    Console.WriteLine(
quot;Adjust product 2 by {delta,3}: {(int)response.StatusCode} {text}");
53}
54await app.StopAsync();
55SqlConnection.ClearAllPools();
56await using (var cleanup = new SqlConnection(Server + ";Database=master"))
57{
58    await cleanup.OpenAsync();
59    await new SqlCommand("DROP DATABASE ShopSmartApi_Procs;", cleanup).ExecuteNonQueryAsync();
60}
61

62// Data/ShopDb.cs
63using Microsoft.EntityFrameworkCore;
64

65public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options);
66

67public class ProductRow                                               // shape of the procedure's result set
68{
69    public int Id { get; set; }
70    public string Name { get; set; } = "";
71    public decimal Price { get; set; }
72}
73

74// Controllers/StockController.cs
75using System.Data;
76using Microsoft.AspNetCore.Mvc;
77using Microsoft.Data.SqlClient;
78using Microsoft.EntityFrameworkCore;
79

80public record AdjustStockRequest(int Delta);
81

82[ApiController]
83[Route("api")]
84public class StockController(ShopDb db) : ControllerBase
85{
86    [HttpGet("categories/{categoryId:int}/products")]
87    public async Task<List<ProductRow>> ByCategory(int categoryId, CancellationToken ct) =>
88        await db.Database
89            .SqlQuery<ProductRow>(
quot;EXEC dbo.usp_GetProductsByCategory @CategoryId = {categoryId}")   // parameterised
90            .ToListAsync(ct);
91

92    [HttpPost("products/{id:int}/stock-adjustments")]
93    public async Task<IActionResult> Adjust(int id, AdjustStockRequest request, CancellationToken ct)
94    {
95        var newStock = new SqlParameter("@NewStock", SqlDbType.Int) { Direction = ParameterDirection.Output };
96        try
97        {
98            await db.Database.ExecuteSqlRawAsync(
99                "EXEC dbo.usp_AdjustStock @ProductId, @Delta, @NewStock OUTPUT",
100                [new SqlParameter("@ProductId", id), new SqlParameter("@Delta", request.Delta), newStock], ct);
101            return Ok(new { productId = id, newStock = (int)newStock.Value });
102        }
103        catch (SqlException ex) when (ex.Number == 50001)                  // the procedure's business error
104        {
105            return Problem(statusCode: StatusCodes.Status422UnprocessableEntity, title: ex.Message);
106        }
107    }
108}

Expected output

Category 1: [{"id":1,"name":"Wireless Mouse","price":799.00},{"id":2,"name":"USB-C Hub","price":1499.00}]
Adjust product 2 by  -3: 200 {"productId":2,"newStock":2}
Adjust product 2 by  20: 200 {"productId":2,"newStock":22}
Adjust product 2 by -50: 422 problem: Stock cannot go below zero (or the product does not exist).

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

The setup creates the table and the two procedures the way a DBA would deliver them.
SqlQuery<ProductRow> runs usp_GetProductsByCategory with a real parameter and maps the columns by name.
ExecuteSqlRawAsync calls usp_AdjustStock with an OUTPUT parameter that carries the new stock level back.
Removing 3 and then adding 20 succeed (5 → 2 → 22).
Removing 50 would go below zero, so the procedure THROWs error 50001, which becomes a 422 problem response.

Real-world use case#

ShopSmart’s warehouse integration calls the warehouse team’s usp_AllocateStock and usp_ConfirmShipment procedures; their THROW codes are mapped to 409 and 422 responses the mobile picking app understands.

Common mistakes#

String-concatenating EXEC calls

quot;EXEC usp_X '{name}'" passed to ExecuteSqlRaw is injectable. Use the interpolated APIs (SqlQuery, ExecuteSql) or SqlParameter.

Composing LINQ after FromSql on a procedure

.Where() after an EXEC cannot be translated (a procedure cannot be a subquery). Filter in the procedure or after ToListAsync.

Catching every SqlException as a business error

Deadlocks and timeouts are not “invalid input”. Check ex.Number.

Best practices#

SqlQuery<T> / FromSql with interpolation
SqlParameter for OUTPUT values
Custom THROW numbers (50000+) for business errors
Version procedures with migrations or scripts

Remember this#

SqlQuery<T>(

quot;EXEC proc @p = {value}") — interpolation = parameters.

OUTPUT → SqlParameter with Direction = Output.

THROW 50001 → catch SqlException Number → 422.

Practice exercise#

Your task: Call usp_GetProductsByCategory with plain ADO.NET (CommandType.StoredProcedure) and return the product names.

Need a hint?

new SqlCommand("dbo.usp_GetProductsByCategory", connection) { CommandType = CommandType.StoredProcedure } and Parameters.AddWithValue or Add("@CategoryId", SqlDbType.Int).

Show solution

C# Illustrative

1// Data/ProcedureData.cs
2using System.Data;
3using Microsoft.Data.SqlClient;
4

5public class ProcedureData(string connectionString)
6{
7    public async Task<List<string>> GetNamesByCategoryAsync(int categoryId, CancellationToken ct)
8    {
9        await using var connection = new SqlConnection(connectionString);
10        await connection.OpenAsync(ct);
11        await using var command = new SqlCommand("dbo.usp_GetProductsByCategory", connection)
12        {
13            CommandType = CommandType.StoredProcedure,
14        };
15        command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
16        await using var reader = await command.ExecuteReaderAsync(ct);
17        var names = new List<string>();
18        while (await reader.ReadAsync(ct)) names.Add(reader.GetString(reader.GetOrdinal("Name")));
19        return names;
20    }
21}

Mini quiz#

Stored Procedures quizMultiple choice Question 1 of 3

quot;EXEC dbo.usp_X @Id = {id}" passed to Database.SqlQuery vulnerable to SQL injection?

Score so far: 0/0

Want more? Practise all ASP.NET Core Web API MCQs · ASP.NET Core Web API interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.

On this page

Related tutorials

Interview questions

In simple words#

Learning objectives#

Explanation#

Syntax#

Code example: Two stored procedures behind API endpoints#

Step-by-step explanation#

Real-world use case#

Common mistakes#

Best practices#

Remember this#

Practice exercise#

Mini quiz#

Related topics#

My notes#