Stored Procedure in SQL Server

📌 Introduction

When working with database in ASP.NET or C#, we often write SQL queries like:

SELECT * FROM Students

But in real projects, developers prefer using:

👉 Stored Procedure

In this article, you will learn:

What is Stored Procedure?
Why we use it?
How to create it?
How to execute it?
Example with output
Advantages
Interview questions

Everything explained in very simple words 😊

🧠 What is a Stored Procedure?

A Stored Procedure is:

👉 A pre-written SQL query
👉 Stored inside the database
👉 Can be executed anytime

Simple meaning:

It is like a saved function inside SQL Server.

🎯 Why We Use Stored Procedure?

Instead of writing SQL query again and again in application code:

We store it once in database and reuse it.

Benefits:

✔ Better performance
✔ More security
✔ Reusable code
✔ Easy maintenance
✔ Cleaner application code

Real-Life Example

Think like this:

You order food from restaurant.

Instead of telling recipe every time,
Chef already saved recipe.

You just say:

“Make Paneer Butter Masala”

Chef knows everything.

That is Stored Procedure.

🛠 How to Create Stored Procedure in

Microsoft SQL Server

Step 1 – Create Table

CREATE TABLE Students
(
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

Step 2 – Insert Data

INSERT INTO Students VALUES (1, 'Rahul', 22);
INSERT INTO Students VALUES (2, 'Amit', 21);
INSERT INTO Students VALUES (3, 'Neha', 23);

Step 3 – Create Stored Procedure

CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT * FROM Students;
END

Now procedure is saved in database.

▶ How to Execute Stored Procedure

EXEC GetAllStudents;

🖥 Output

Id   Name    Age
1    Rahul   22
2    Amit    21
3    Neha    23

🧠 Stored Procedure with Parameter

We can also pass values.

Example:

CREATE PROCEDURE GetStudentById
    @Id INT
AS
BEGIN
    SELECT * FROM Students WHERE Id = @Id;
END

Execute:

EXEC GetStudentById 2;

🖥 Output

Id   Name   Age
2    Amit   21

🔐 Why Stored Procedure is More Secure?

If we write query directly in C#:

string query = "SELECT * FROM Students WHERE Id=" + id;

It may cause SQL Injection.

But stored procedure:

✔ Protects against SQL Injection
✔ Safer

📊 Simple Difference – Query vs Stored Procedure

Normal Query	Stored Procedure
Written in code	Stored in DB
Less secure	More secure
Repeated code	Reusable
Hard to manage	Easy to maintain

🎯 When Should You Use Stored Procedure?

Use it when:

✔ Working with large data
✔ Building enterprise application
✔ Need security
✔ Need better performance
✔ Using ASP.NET with SQL Server

💡 Interview Questions

Common questions:

What is Stored Procedure?
Difference between Function and Stored Procedure?
Can Stored Procedure return value?
What is parameter in Stored Procedure?

🏁 Conclusion

Stored Procedure is a powerful feature of SQL Server.

It helps to:

✔ Improve performance
✔ Increase security
✔ Reuse SQL logic
✔ Keep application clean

If you are learning ASP.NET or C#, you must understand Stored Procedures.