What are Stored Procedures? how can we use them?

Stored procedure is organized SQL code. Instead of writing sql query again and again, we just write that query once and save it as stored procedure. Whenever we need this code we just call procedure and get back the result that we need (just like functions). Store Procedure is used to retrieve data, modify data, and delete data from Database table. Stored procedures are secure to use as it avoids any injection attacks and it reduces the coding mistakes. Stored procedure has some syntax and parts. It has:

Name: it can be any meaningful name

Parameter: starts from @ sign

Body: starts from “CREATE PROCEDURE” clause

Here is a simple example of student table. Let’s assuming we want to view semester details of any specific student. So we will send studentID to that function which will retrieve all its information. The code is below.

CREATE PROCEDURE spGetStudent (@StudentID int) 

AS

      SELECT FirstName, LastName, SemsterNo

      FROM Student

      WHEREStudentID=@StudentID

Here spGetStudent  is name of procedure. @StudentID int is parameter StudentID of type int; following the body of procedure.

Hope it will help you now in using stored procedures.