CREATE PROCEDURE, DROP PROCEDURE statement, call a stored procedure in SQL

CREATE PROCEDURE statement: CREATE PROCEDURE statement is used to create a stored procedure. It also defines number and data types of its parameters, data types and names of local variables used by the stored procedure, and sequence of statements which will be executed when this particular procedure is called.

For Example:

create procedure add_empl (
emp_name in varchar(30),
emp_num in integer,
emp_salary in number(20,2),
emp_perks in number(20,2))
insert into employees (empl_name, empl_num, empl_salary, empl_perks)
values (emp_name, emp_num, emp_salary, emp_perks);

DROP PROCEDURE statement: DROP PROCEDURE statement is used for dropping a stored procedure.

For Example:

drop procedure add_empl

To call a stored procedure: For calling a stored procedure, EXECUTE or EXEC statement is used. Values of parameters should be used in order as it was declared in CREATE PROCEDURE statement.

For Example:

execute add_empl(‘Max D’, 115, 35000.00, 500.00)


exec add_empl(‘Max D’, 115, 35000.00, 500.00)

If the procedure is called from another procedure or trigger, then EXECUTE or EXEC keyword is not required:

add_empl(‘Max D’, 115, 35000.00, 500.00)

Leave a Reply