SQL STORE PROCEDURES
A STORE PROCEDURES in SQL is a collection of one or more SQL statements or commands that are stored in a database     and can be executed as a single unit.
Syntax:Â Â Â Â
          CREATE PROCECURE procedure_name                                                                       as                                                                                                     begin                                                                                                     sql_statement                                                                                       End;
Let’s understand the store procedure with an example.
     Table EMP
     Table Project
Example:
  Objective: In this example, we will create a Store Procedure that will retrieve records from the Emp Table where the                location is Texas.Â
           CREATE PROCEDURE SP_TEXAS_R                                                                          As                                                                                                    Begin                                                                                                       SELECT * FROM Tbl_Emp                                                                                         WHERE Location = ‘Texas’                                                                     End;
  Execute Store Procedure:
           EXEC SP_TEXAS_R
  Output:
Example:
  Objective: In this example, we will use SQL joins to store procedures.Â
 CREATE PROCEDURE SP_TEXAS_R                                                                As                                                                                         Begin         Â
                SELECT e.FirstName, e.LastName , e.Emailaddress, P.Project                                                             FROM Tbl_Emp as e INNER JOIN Tbl_Project as p                                                             on e.ecode = p.ecode
 End
  Execute store procedure:
           EXEC  SP_TEXAS_R;
  Output:
Example:
  Objective: In this example, we will use input parameters in store procedure.Â
 CREATE PROCEDURE SP_TEXAS_R(@ecode as int)                                                   As                                                                                         Begin         Â
                SELECT e.FirstName, e.LastName , e.Emailaddress, P.Project                                                             FROM Tbl_Emp as e INNER JOIN Tbl_Project as p                                                             on e.ecode = p.ecode                                                                                    WHERE e.Ecode = @eode
 End
  Execute store procedure:
           EXEC  SP_PR @ecode = 1001;
  Output:
Example:
Objective: In this example, we will see how to drop the store procedure.
           DROP PROCEDURE SP_TEXAS_RÂ
Output parameters in a stored procedure allow you to return values from the procedure.
When creating the stored procedure, you define an output parameter using the OUTPUT keyword. This parameter will hold the value that you want to return.
Example:
  Objective: In this example, we will use output parameters in the store procedure.Â
 CREATE PROCEDURE SP_PR_OUT(@Dept as varchar(20) , @EmployeeCount as int OUTPUT)                 As                                                                                         Begin         Â
                SELECT @EmployeeCount = COUNT(*)                                                                         FROM Tbl_Emp                                                                                            WHERE Department = @dept
 End
  Execute output store procedure:
           Â
 Â
Â
Output:
DECLARE @Count INT;
EXEC SP_PR_OUT @dept = ‘IT’ , @EmployeeCount = @Count OUTPUT
Print @Count
