---
title: "What is a stored procedure in SQL Server and how to create one?"  
description: "What is a stored procedure in SQL Server and how to create one?"  
author: "Utpal Vishwas"  
published: 2023-05-16  
updated: 2023-11-21  
canonical: https://www.mindstick.com/forum/158350/what-is-a-stored-procedure-in-sql-server-and-how-to-create-one  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# What is a stored procedure in SQL Server and how to create one?

What is a [stored](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-the-student-table) [procedure in SQL](https://www.mindstick.com/forum/33533/how-to-create-stored-procedure-in-sql-server) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) and how to create one?

## Replies

### Reply by Aryan Kumar

In [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server), a [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) is a precompiled collection of one or more SQL statements that perform a specific task. Stored procedures are used to encapsulate and organize SQL code, improve code reusability, and enhance security. They can accept input parameters, return output parameters, and execute a series of SQL statements as a single unit of work.

Here's how you can create a simple stored procedure in SQL Server:

## Syntax:

```plaintext
CREATE PROCEDURE procedure_name
    @parameter1 datatype1,
    @parameter2 datatype2,
    -- Add more parameters as needed
AS
BEGIN
    -- SQL statements go here
END;
```

**Example:** Let's create a stored procedure that retrieves employees from a table based on a specified department:

```plaintext
CREATE PROCEDURE GetEmployeesByDepartment
    @deptName NVARCHAR(50)
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @deptName;
END;
```

In this example:

**CREATE PROCEDURE GetEmployeesByDepartment**: This line declares the name of the stored procedure.

**@deptName NVARCHAR(50)**: Here, **@deptName** is an input parameter of type **NVARCHAR(50)** that represents the department name.

**AS**: Indicates the beginning of the stored procedure's body.

**SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Department = @deptName;**: This is the SQL statement inside the stored procedure. It retrieves employee information based on the specified department.

To execute the stored procedure, you can use the **EXEC** or **EXECUTE** statement:

```plaintext
EXEC GetEmployeesByDepartment @deptName = 'HR';
```

This executes the **GetEmployeesByDepartment** stored procedure with the department name parameter set to 'HR'.

### Advantages of Stored Procedures:

## Code Reusability:

- Stored procedures can be reused in multiple parts of an application, reducing the need to duplicate code.

## Improved Performance:

- Stored procedures are precompiled and stored in the database, leading to better performance compared to dynamically executed SQL statements.

## Security:

- Permissions on tables can be restricted, and users can be granted permission to execute stored procedures without direct access to underlying tables.

## Modularity:

- Large and complex tasks can be divided into smaller, more manageable stored procedures, making the code easier to maintain.

## Encapsulation:

- Stored procedures encapsulate the logic, making it easier to update and maintain the code without affecting the application.

Remember to choose meaningful names for your stored procedures and follow best practices to enhance code readability and maintainability.


---

Original Source: https://www.mindstick.com/forum/158350/what-is-a-stored-procedure-in-sql-server-and-how-to-create-one

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