Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Executing Commands

Stored Procedures

Many company databases expose their logic as stored procedures. ADO.NET calls them with just one extra line.

Intermediate 5 min Lesson 13 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:Parameterized QueriesSQL Server: Stored Procedures
0/32 in ADO.NET
On this page · 5 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Syntax
  5. Code example
  6. Step by step
  7. Real-world use
  8. Common mistakes
  9. Best practices
  10. Remember this
  11. Practice exercise
  12. Mini quiz
  13. Related topics
  14. My notes
Reading time
5 min
Difficulty
Intermediate
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

Instead of reading out the whole recipe over the phone, you say “make dish #7” — call the stored procedure by name with its parameters.

CommandType.StoredProcedure
Call a procedure by name.
Parameter names
Must match the procedure’s.
SET NOCOUNT ON
Fewer messages; NonQuery returns -1.

Learning objectives#

  • Create a simple stored procedure
  • Call it with CommandType.StoredProcedure
  • Read the rows it returns
  • Understand SET NOCOUNT ON

Explanation#

Set CommandText to the procedure name and CommandType to CommandType.StoredProcedure. Parameter names must match the procedure’s parameters.

A procedure that ends with SELECT returns rows: use ExecuteReader. One that only changes data uses ExecuteNonQuery.

SET NOCOUNT ON stops “n rows affected” messages, reducing network chatter — but then ExecuteNonQuery returns -1, so use output parameters for counts.

Syntax#

SQL Illustrative
CREATE OR ALTER PROCEDURE dbo.GetProductsByCategory
  @CategoryId INT,
  @MinStock   INT = 0
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Id, Name, Price, Stock
  FROM Products
  WHERE CategoryId = @CategoryId AND Stock >= @MinStock
  ORDER BY Name;
END

Code example: Calling the procedure#

C#CategoryProducts.cs Illustrative
await using var conn = new SqlConnection(cs);
await conn.OpenAsync();


await using var cmd = new SqlCommand("dbo.GetProductsByCategory", conn)
{
    CommandType = CommandType.StoredProcedure,
};
cmd.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 1;
// @MinStock is optional — its default (0) is used when we don't add it


await using var r = await cmd.ExecuteReaderAsync();
while (await r.ReadAsync())
    Console.WriteLine(
quot;{r.GetString(1)}: ₹{r.GetDecimal(2)} ({r.GetInt32(3)} in stock)");
Expected output
Keyboard: ₹2200.00 (40 in stock)
Laptop: ₹55000.00 (8 in stock)
Mouse: ₹1400.00 (120 in stock)

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#

  1. The procedure filters products by category and minimum stock.
  2. CommandType.StoredProcedure tells SqlClient to call it as a procedure (an RPC call).
  3. Only the required parameter is supplied; the optional one uses its default.
  4. The SELECT inside the procedure is read with a normal reader.

Real-world use case#

A bank’s database team owns the procedures and their permissions; application developers get EXECUTE rights only, never direct table access.

Common mistakes#

CommandText = "EXEC dbo.GetProducts @id"

Works, but CommandType.StoredProcedure is clearer and avoids parsing.

Misspelled parameter names

You get “Procedure expects parameter '@CategoryId', which was not supplied”. Names must match.

Best practices#

  • Use CommandType.StoredProcedure
  • Schema-qualify names (dbo.)
  • Use SET NOCOUNT ON in procedures
  • Grant EXECUTE instead of table permissions

Remember this#

Name in CommandText + CommandType.StoredProcedure.

Parameter names must match.

NOCOUNT ON → ExecuteNonQuery returns -1.

Practice exercise#

Your task: Call dbo.GetProductsByCategory for category 2 with @MinStock = 10.

Show solution
C# Illustrative
cmd.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 2;
cmd.Parameters.Add("@MinStock", SqlDbType.Int).Value = 10;

Mini quiz#

Stored Procedures quizMultiple choice Question 1 of 1

Which property must be set to call a procedure by name?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

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