Text copied!
SQL Store Procedure
In SQL, Store Procedure (SP) can be defined as a block/set of SQL statements that can be saved and executed whenever needed. It is used to perform a specific task or accomplish a particular purpose.
It can be reused through multiple projects which reduces code redundancy. 'Code redundancy' refers to the practice of writing the same code multiple times within a project.
Here's the syntax :
CREATE PROCEDURE procedure_name
@parameter1 data_type, -- optional
@parameter2 data_type, -- optional
...
AS
BEGIN
-- Write SQL statements here to perform desired operation
END
• Specify the store_procedure_name after the 'CREATE PROCEDURE' keyword.
• Specify the parameter name using the "@" prefix and its data type. store procedure can be created without any parameter.
• 'AS BEGIN' keyword indicates that the store procedure body has started.
• 'END' keyword indicates that the store procedure body has ended.
Input Parameter :
In SQL Server, store procedure accepts many or no parameter. A parameter is a value that is passed into a store procedure to perform specific task.
Calling store procedure :
Calling store procedure simply means executing or running it. To call a store procedure in SQL Server, you can use the 'EXEC' statement in a SQL query.
Here's the syntax :
Without Parameter -
EXEC store_procedure_name;
With Parameter -
EXEC store_procedure_name @Parameter = Value;
• Specify the store procedure name after the 'EXEC' keyword.
• Specify the parameter name and input value after store procedure name.
Here's an example of Store Procedure without Parameter :
[i] Let's assume you want to create a store procedure that retrieve only 'Male' employees data from [Employees] table.
[ii] Run below SQL statement :
CREATE PROCEDURE GetAllMaleEmployees
AS
BEGIN
SELECT * FROM Employees
WHERE Gender = 'Male';
END
[iii] Above statement will create a new store procedure named as 'GetAllMaleEmployees'.
[iv] Next, let's execute this store procedure. Run below SQL statement :
EXEC GetAllMaleEmployees;
[v] Above statement retrieves employee data from [Employees] table whose gender is specified as 'Male'.
Here's an example of Store Procedure with a Parameter :
[i] Let's assume you want to create a store procedure that accepts employee_id as input parameter from [Employees] table.
That means while executing store procedure, you can manually provide any specific employee_id value for this input parameters and thus you can control its execution based on your requirements.
[ii] Run below SQL statement :
CREATE PROCEDURE GetAllEmployeesById
@Employee_Id INT
AS
BEGIN
SELECT * FROM Employees
WHERE Employee_Id = @Employee_Id;
END
[iii] Above statement will create a new store procedure named as 'GetAllEmployeesById' that accepts employee_Id as input parameter from [Employees] table.
[iv] Next, let's execute this store procedure. Run below SQL statement :
EXEC GetAllEmployeesById @Employee_Id = 1;
[v] In above statement, we passed value '1' as parameter value. This will retrieve employee data from [Employees] table where employee_id is equal to '1'.
