Saturday 20 July 2013

Oracle Stored Procedure Versus Sql server Stored Procedure - Part 1

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