---
title: "Uses of Stored Procedure in Database"  
description: "It is a set of structured query language statement with given name. It is stored in the database in the compiled form.It is store in the compile form"  
author: "Elena Glibart"  
published: 2017-01-20  
updated: 2018-03-17  
canonical: https://www.mindstick.com/blog/11274/uses-of-stored-procedure-in-database  
category: "database"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Uses of Stored Procedure in Database

It is a set of [structured query language](https://www.mindstick.com/blog/11168/introduction-to-sql) statement with given name. It is stored in the database in the compiled form. It is store in the compile form that’s why it can be shared by many program. It accept [input and output](https://www.mindstick.com/forum/161309/what-are-the-features-of-the-console-class-for-input-and-output-in-java) parameter. It increase network traffic and increase performance. While modifying our procedure our client get updated procedure.

**Syntax for [Stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net)**

-- Syntax for [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) [Stored Procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application)

```
CREATE  { PROC | PROCEDURE } procedure name    [ { @parameter data_type }          [ VARYING ] [ = default ] [ OUT |
OUTPUT |       ]  AS { [ BEGIN ]
sql_statement [;] [ ...n ] [ END ] }  [;] 
```

<procedure_option> ::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE AS Clause ]

Procedure name-It is the name of stored procedure

\

Create-this keyword is use for creating

\

PROC|PROCEDURE-we can use any [one of them](https://answers.mindstick.com/qa/47593/what-are-the-prevalent-water-proofing-systems-write-a-short-notes-on-any-one-of-them) for creating procedure

\
OUT|

OUTPUT-It tell that our declared parameter is an output parameter.

\

For declaring parameter we use @ sign then parameter and its

\

data type. When we call procedure then the value of parameter that is

\

declared int the procedure should be pass.

We can use Procedure or Proc [while creating](https://www.mindstick.com/forum/105382/which-constraints-we-can-use-while-creating-a-database-in-sql) procedure.

Now we take a example to understand with input parameterNow we [create table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) in database name E_Salary and Inserted few records

![Uses of Stored Procedure in Database](https://www.mindstick.com/blogs/6fc168f0-1b86-4d2f-b176-0879270a7630/images/b419c824-dfde-4ffe-a84c-9e6ddfb1f410.png)\

Suppose we want to make procedure for getting salary by id

Then we do following code for crating procedure

```
SET QUOTED_IDENTIFIER
ONGO Create proc [dbo].[Getsalary]@EId intASbeginselect salary from
Test.dbo.E_Salary
where Id = @EIdend
```

Now for testing and executive we use EXEC and procedure name and here we pass the id of getting salary for particular id

```
EXEC dbo.Getsalary @EID = 2
```

## Output

\

![Uses of Stored Procedure in Database](https://www.mindstick.com/blogs/6fc168f0-1b86-4d2f-b176-0879270a7630/images/b864ab8e-c86e-4619-b71c-31686b5ed0bc.png)\

Input and output and basically done same thing we use out or output

\

keyword for indicating that it [return value](https://www.mindstick.com/forum/33675/how-to-convert-return-value-of-abmulivaluecopylabelatindex-into-nsstring-in-ios).

Now we will modify in the same procedure to declare out parameter

\

\

**Ex**

```
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER
ONGOALTER proc [dbo].[Getsalary]@EId int,@Ename varchar(50) outputASbeginselect salary from
Test.dbo.E_Salary
where Id = @EIdend
```

we have declare Enema as Output parameter now we will execute this procedure to test

```
EXEC dbo.Getsalary @EID = 1,@Ename=amit
```

\

![Uses of Stored Procedure in Database](https://www.mindstick.com/blogs/6fc168f0-1b86-4d2f-b176-0879270a7630/images/776af6e8-aa1c-4065-828e-29e0ceb0478f.png)\

**Advantage of Stored Procedure**

**1-**Stored Procedure provide security for our data and program

**2-**When we use stored procedure our execution of program become fast means it allow faster execution.

**3-**We can modify easily if we use stored procedure.

\

---

Original Source: https://www.mindstick.com/blog/11274/uses-of-stored-procedure-in-database

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