How to Write a Stored Procedure in SQL Server

In this article you will learn 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

  1. Create Procedure CalcuteMath  
  2. (  
  3.    @FirstNo int,  
  4.    @SecondNo int  
  5. )  
  6. As  
  7. Begin  
  8. Select @FirstNo + @SecondNo AS SumResult  
  9. Select @FirstNo - @SecondNo AS SubtractResult  
  10. Select @FirstNo * @SecondNo AS MultiplyResult  
  11. 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:

store procedure

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

Execute CalcuteMath 5,4
execute storeprocedure

I hope this article is helpful for you..

Thanks :)