---
title: "Stored Procedures in SQL – Complete Guide"  
description: "A Stored Procedure in SQL is a precompiled set of SQL statements that is stored in the database and can be executed whenever needed."  
author: "Ravi Vishwakarma"  
published: 2026-03-23  
updated: 2026-03-24  
canonical: https://answers.mindstick.com/blog/124/stored-procedures-in-sql-complete-guide  
category: "database"  
tags: ["sql server", "database"]  
reading_time: 3 minutes  

---

# Stored Procedures in SQL – Complete Guide

### 1. What is a Stored Procedure?

A **[Stored Procedure](https://www.mindstick.com/articles/36/stored-procedure-in-microsoft-sql-server)** in **SQL** is a **precompiled set of SQL statements** that is stored in the [database](https://www.mindstick.com/articles/71/how-to-create-xml-file-of-database-in-c-sharp) and can be executed whenever needed.\
It helps to **reuse code, [improve performance](https://www.mindstick.com/forum/158717/how-can-you-optimize-database-queries-and-improve-performance-in-asp-dot-net-mvc), and increase [security](https://www.mindstick.com/articles/43813/new-security-technologies)**.

In simple words:

> A Stored Procedure is like a **function inside the database** that you can call again and again.

Example use:

- Insert data
- Update data
- Delete data
- Complex queries
- [Business](https://www.mindstick.com/articles/44023/free-technology-for-small-businesses) logic

### 2. Why use Stored Procedures?

| Benefit | Explanation |
| --- | --- |
| Performance | Precompiled → runs faster |
| Reusability | Write once, use many times |
| Security | Hide table logic from users |
| Easy Maintenance | Change in one place |
| Less Network Traffic | Only call procedure |

### 3. Syntax of Stored Procedure (SQL Server)

```plaintext
CREATE PROCEDURE ProcedureName
AS
BEGIN
    SQL Statements
END
```

Example:

```plaintext
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees
END
```

Run procedure:

```plaintext
EXEC GetEmployees
```

### 4. Stored Procedure with Parameters

We can pass values to [stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application).

```plaintext
CREATE PROCEDURE GetEmployeeById
    @Id INT
AS
BEGIN
    SELECT * FROM Employees WHERE Id = @Id
END
```

Execute:

```plaintext
EXEC GetEmployeeById 5
```

### 5. Stored Procedure with Multiple Parameters

```plaintext
CREATE PROCEDURE GetEmployee
    @Id INT,
    @Name VARCHAR(50)
AS
BEGIN
    SELECT *
    FROM Employees
    WHERE Id = @Id OR Name = @Name
END
```

Run:

```plaintext
EXEC GetEmployee 1, 'Rahul'
```

### 6. Stored Procedure for INSERT

```plaintext
CREATE PROCEDURE AddEmployee
    @Name VARCHAR(50),
    @Salary INT
AS
BEGIN
    INSERT INTO Employees(Name, Salary)
    VALUES(@Name, @Salary)
END
```

Run:

```plaintext
EXEC AddEmployee 'Amit', 30000
```

### 7. Stored Procedure for UPDATE

```plaintext
CREATE PROCEDURE UpdateSalary
    @Id INT,
    @Salary INT
AS
BEGIN
    UPDATE Employees
    SET Salary = @Salary
    WHERE Id = @Id
END
```

Run:

```plaintext
EXEC UpdateSalary 1, 50000
```

### 8. Stored Procedure for DELETE

```plaintext
CREATE PROCEDURE DeleteEmployee
    @Id INT
AS
BEGIN
    DELETE FROM Employees
    WHERE Id = @Id
END
```

Run:

```plaintext
EXEC DeleteEmployee 1
```

### 9. Stored Procedure with IF ELSE

```plaintext
CREATE PROCEDURE CheckSalary
    @Salary INT
AS
BEGIN
    IF @Salary > 50000
        PRINT 'High Salary'
    ELSE
        PRINT 'Low Salary'
END
```

### 10. Stored Procedure with TRY CATCH

```plaintext
CREATE PROCEDURE SafeInsert
    @Name VARCHAR(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Employees(Name)
        VALUES(@Name)
    END TRY
    BEGIN CATCH
        PRINT 'Error Occurred'
    END CATCH
END
```

### 11. Difference Between Stored Procedure and Function

| Feature | Stored Procedure | Function |
| --- | --- | --- |
| [Return value](https://www.mindstick.com/forum/33675/how-to-convert-return-value-of-abmulivaluecopylabelatindex-into-nsstring-in-ios) | Optional | Must return |
| Can use INSERT/UPDATE | Yes | Limited |
| Can return table | Yes | Yes |
| Can use TRY CATCH | Yes | No |
| Call | EXEC | SELECT |

### 12. When to Use Stored Procedure

Use stored procedure when:

- Complex query
- Repeated query
- [Business logic](https://answers.mindstick.com/qa/30543/what-is-business-logic) in DB
- Need security
- Need [better performance](https://www.mindstick.com/forum/156505/where-do-i-put-my-javascript-code-in-my-file-for-better-performance)

### 13. Real Life Example

Login system

```plaintext
CREATE PROCEDURE LoginUser
    @Email VARCHAR(100),
    @Password VARCHAR(100)
AS
BEGIN
    SELECT *
    FROM Users
    WHERE Email = @Email
    AND Password = @Password
END
```

Run:

```plaintext
EXEC LoginUser 'test@gmail.com', '1234'
```

### 14. Conclusion

Stored Procedures are very important in SQL because they:

- Improve performance
- Make code reusable
- Increase security
- [Reduce errors](https://answers.mindstick.com/qa/114472/how-can-emerging-digital-solutions-enhance-patient-outcomes-and-reduce-errors-in-healthcare)
- Used in real projects

---

Original Source: https://answers.mindstick.com/blog/124/stored-procedures-in-sql-complete-guide

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.