Let we start the article with simple examples.
Oracle :
In Oracle Create or Replace is a keyword is used to create the stored procedure , Replace indicates that the procedure with that name already exists replace with this new one
CREATE OR REPLACE PROCEDURE sample_sp
(
emp_no IN number,
emp_name OUT varchar2
)
IS
BEGIN
SELECT employee_name INTO emp_name
FROM Employee
Where employee_id = emp_no
END sample_sp;
To Execute the Above Sp We have to write a simple PL\SQL
Declare
name varchar2(40);
BEGIN
sample_sp(2,name);
dbms_output.put_line(name);
END
Sql Server
In Sql Server Create is the Keyword used to create the Stored Procedure and if you want to overwrite the existing stored procedure used ALTER keyword instead of CREATE.
CREATE PROCEDURE sample_sp
(
@id int,
@name varchar(30) out
)
AS
BEGIN
SELECT @name = employee_name
FROM Employee
WHERE employee_id = @id
END
How to Execute the Stored Procedure
Declare @emp_name varchar(30)
EXEC sample_sp 2,@emp_name out
Select @emp_name
Oracle :
In Oracle Create or Replace is a keyword is used to create the stored procedure , Replace indicates that the procedure with that name already exists replace with this new one
CREATE OR REPLACE PROCEDURE sample_sp
(
emp_no IN number,
emp_name OUT varchar2
)
IS
BEGIN
SELECT employee_name INTO emp_name
FROM Employee
Where employee_id = emp_no
END sample_sp;
To Execute the Above Sp We have to write a simple PL\SQL
Declare
name varchar2(40);
BEGIN
sample_sp(2,name);
dbms_output.put_line(name);
END
Sql Server
In Sql Server Create is the Keyword used to create the Stored Procedure and if you want to overwrite the existing stored procedure used ALTER keyword instead of CREATE.
CREATE PROCEDURE sample_sp
(
@id int,
@name varchar(30) out
)
AS
BEGIN
SELECT @name = employee_name
FROM Employee
WHERE employee_id = @id
END
How to Execute the Stored Procedure
Declare @emp_name varchar(30)
EXEC sample_sp 2,@emp_name out
Select @emp_name