There are syntactical and functional differences between Oracle and SQL Server when it comes to writing stored procedures.
Both use different procedural languages:
Oracle uses PL/SQL (Procedural Language/SQL)
SQL Server uses T-SQL (Transact-SQL)
Here's a comparison highlighting key syntactical differences:
1. Procedure Declaration
Oracle:
-------
CREATE OR REPLACE PROCEDURE proc_name (
p_id IN NUMBER,
p_name OUT VARCHAR2
)
AS
BEGIN
-- PL/SQL block
END;
SQL Server:
-----------
CREATE PROCEDURE proc_name
@id INT,
@name NVARCHAR(100) OUTPUT
AS
BEGIN
-- T-SQL block
END;
2. Variable Declaration
Oracle:
-------
DECLARE
v_total NUMBER;
SQL Server:
-----------
DECLARE @total INT;
3. Calling a Stored Procedure with Output Parameters
Oracle:
-------
DECLARE
v_name VARCHAR2(100);
BEGIN
proc_name(101, v_name);
DBMS_OUTPUT.PUT_LINE(v_name);
END;
SQL Server:
-----------
DECLARE @name NVARCHAR(100);
EXEC proc_name @id = 101, @name = @name OUTPUT;
PRINT @name;
4. Exception Handling
Oracle:
-------
BEGIN
-- statements
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error');
END;
SQL Server:
-----------
BEGIN TRY
-- statements
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH
STORED PROCEDURES: ----------------------------------
* Write a program to get department-wise salaries.
SQL:
Create or Alter procedure proc_depwise_salaries
@in_deptno int
AS
Begin
Select sal from emp where deptno=@in_deptno;
end;
Execute proc_depwise_salaries @in_deptno=10
ORACLE:
Create or replace Procedure Get_deptwises_sal (
input_deptno IN NUMBER )
AS
BEGIN
select * from emp where deptno=in_deptno;
end;
BEGIN
Get_deptwises_sal(10);
END;
/
Declaring Parameters in Stored Procedures:
-----------------------------------------------------
When you define input and output parameters for a stored procedure in SQL Server, you do not use the DECLARE keyword.
Example:
------------
CREATE PROCEDURE GetDeptSalaryStats
@inputDeptNo INT, -- Input parameter
@totalSalary DECIMAL(18,2) OUTPUT, -- Output parameter
@averageSalary DECIMAL(18,2) OUTPUT -- Output parameter
AS
BEGIN
-- No need to DECLARE again, these are already declared as parameters
SELECT
@totalSalary = SUM(sal),
@averageSalary = AVG(sal)
FROM emp
WHERE deptno = @inputDeptNo;
END;
Note:
-------
* @inputDeptNo, @totalSalary, and @averageSalary are parameters, not local variables.
* When parameters are declared in the procedure header, DECLARE is not used.
* You can use them directly inside the body of the procedure.
You use DECLARE only inside the procedure body or script to declare local variables, like this:
DECLARE @x INT = 10;
This is not a parameter but a local variable, usable only in that block or batch.
| Usage Context | Syntax Example | Uses DECLARE? | Scope
| ------------------------- | ----------------------------------- | ------------- | -----------------
| Procedure parameters | @deptno INT, @totSal DECIMAL OUTPUT | No | Across procedure
| Local variable | DECLARE @x INT = 5; | Yes | Inside procedure
| Ad-hoc script variable | DECLARE @deptno INT = 10; | Yes | In script/session
