How to Write a Stored Procedure in SQL Server

In this article we learn what a Stored Procedure is and how to make a Stored Procedure in SQL Server.

A Stored Procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a Stored Procedure and then just call the Stored Procedure to execute the SQL code that you saved as part of the Stored Procedure.
Advantages of using the Stored Procedure

1. A Stored Procedure is compiled once when it is created. If we alter that Stored Procedure then it is recompiled when the SQL statement needs to be compiled every time whenever it is sent for execution.
   
   
2. Stored Procedures can be used by multiple users and multiple clients when writing a SQL statement every time is a pain and it not safe.
   
   
3. In a Stored Procedure we implement security, in other words we can grant/revoke permission to execute the Stored Procedure instead of grant/revoke permission on the table.
   
   
4. We can implement a transaction in a Stored Procedure.

Syntax

Create Procedure CalcuteMath
(
   @FirstNo int,
   @SecondNo int
)
As
Begin
Select @FirstNo + @SecondNo AS SumResult
Select @FirstNo - @SecondNo AS SubtractResult
Select @FirstNo * @SecondNo AS MultiplyResult
End

You can find the location of the Stored Procedure in SQL Server Management Studio. I am using SQL Server 2008.

Database → Programmabilility → Stored Procedures → Stored Procedure Name

The database name is test as in the following:



Let's execute the Stored Procedure using the following code and see the output.

Execute CalcuteMath 5,4


I hope this article is helpful for you..

Thanks :)