Thursday, July 24, 2025

Oracle and SQL Server stored procedures differences

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 




No comments:

Post a Comment

Different types of indexes in SQL Server and Oracle

* What are the different types of indexes in Sql server and Oracle? Identify the differences. In SQL Server and Oracle, indexes are crucial ...