Learn / Oracle → SQL Server / Stored Procedures & PL/SQL vs T-SQL
Lesson 4 of 5

Stored Procedures & PL/SQL vs T-SQL

PL/SQL and T-SQL do the same job — procedural logic wrapped around SQL — but structure it differently. PL/SQL wraps everything in an explicit DECLARE/BEGIN/END block and groups related procedures into packages. T-SQL has no block delimiters around the whole procedure body and no package concept at all — it uses schemas for the organizational job packages do in Oracle.

💡 The Bottom Line

A PL/SQL procedure is a DECLARE/BEGIN/END; block with variables declared up front. A T-SQL procedure just starts running statements after AS — variables are declared inline with DECLARE @var TYPE wherever you need them, no separate declaration section required. Oracle groups related procedures into a named PACKAGE; SQL Server has no equivalent grouping construct — you organize procedures using schemas (sales.GetOrder, hr.GetEmployee) instead.

Basic procedure structure

Oracle PL/SQL
CREATE OR REPLACE PROCEDURE give_raise (
  p_emp_id IN NUMBER,
  p_pct    IN NUMBER
) AS
  v_old_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_old_salary
  FROM employees
  WHERE employee_id = p_emp_id;

  UPDATE employees
  SET salary = v_old_salary * (1 + p_pct/100)
  WHERE employee_id = p_emp_id;

  COMMIT;
END give_raise;
/
SQL Server T-SQL
CREATE OR ALTER PROCEDURE hr.GiveRaise
  @EmpId INT,
  @Pct   DECIMAL(5,2)
AS
BEGIN
  DECLARE @OldSalary DECIMAL(10,2);

  SELECT @OldSalary = salary
  FROM employees
  WHERE employee_id = @EmpId;

  UPDATE employees
  SET salary = @OldSalary * (1 + @Pct/100)
  WHERE employee_id = @EmpId;
END;

📋 Key differences

  • PL/SQL parameters are plain names (p_emp_id); T-SQL parameters are always prefixed with @ (@EmpId) — and that @ prefix applies to every local variable too, not just parameters.
  • PL/SQL separates DECLARE (variables) from BEGIN (executable code). T-SQL has no separate declare section — DECLARE @OldSalary DECIMAL(10,2); is just an executable statement that can appear anywhere before first use.
  • PL/SQL's SELECT ... INTO variable reads one row into a variable. T-SQL's equivalent is SELECT @variable = column ... — the INTO keyword in T-SQL means something else entirely (see the gotcha below).
  • PL/SQL's %TYPE lets a variable inherit a column's exact data type (employees.salary%TYPE) so it stays in sync if the column changes. T-SQL has no direct equivalent — you declare the variable's type explicitly and keep it manually in sync, though table-valued parameters and sp_help can reduce the pain in some cases.
  • Oracle procedures are transaction-aware by explicit COMMIT/ROLLBACK. SQL Server's default autocommit behavior is covered in the next lesson — it changes when you need an explicit COMMIT at all.

⚠️ Gotcha

T-SQL has a SELECT ... INTO new_table FROM ... syntax that creates a brand new table from a query's results — it looks exactly like Oracle's "read one row into a variable" idiom but does something completely different. To assign a query result into a variable in T-SQL, use SELECT @var = column FROM ... WHERE ... (assignment on the left of =), never SELECT column INTO @var FROM ... — that's the table-creation form, and passing it a variable name is a syntax error, not a quiet no-op.

Packages vs schemas

Oracle
CREATE OR REPLACE PACKAGE hr_pkg AS
  PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER);
  FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
END hr_pkg;
/

-- called as:
hr_pkg.give_raise(100, 5);
SQL Server
CREATE PROCEDURE hr.GiveRaise
  @EmpId INT, @Pct DECIMAL(5,2) AS ...;

CREATE FUNCTION hr.GetSalary(@EmpId INT)
  RETURNS DECIMAL(10,2) AS ...;

-- called as:
EXEC hr.GiveRaise @EmpId = 100, @Pct = 5;

📋 Key differences

  • A PL/SQL package bundles related procedures/functions (plus private helpers and package-level state) under one name, with a separate spec and body. T-SQL has no package construct at all — every procedure and function is its own standalone object.
  • SQL Server's organizational unit is the schema (like a namespace): hr.GiveRaise, hr.GetSalary, sales.CreateOrder. It groups objects for permissions and naming, but each object is still created, altered, and dropped independently — there's no single "package body" to redeploy as a unit.
  • Calling a procedure uses EXEC (or EXECUTE) in T-SQL, versus calling it directly by name (optionally via EXECUTE IMMEDIATE or in a PL/SQL block) in Oracle.
  • Package-level private state (variables declared in a package body, shared across calls in the same session) has no T-SQL equivalent — the closest substitutes are temp tables, table variables, or session-scoped context info, each with different lifetimes.

🔧 Try It Yourself

Take a small PL/SQL procedure you've written that reads one row with SELECT ... INTO`` and updates it. Rewrite it as a T-SQL stored procedure: prefix every variable with @, replace SELECT col INTO var with SELECT @var = col, drop the %TYPE reference for an explicit type, and put it in a schema name instead of a package name.

🙋 There Are No Dumb Questions

Q: If T-SQL has no packages, how do I group "private" helper procedures that shouldn't be called from outside a module?
A: There's no enforced privacy mechanism like a package body's hidden implementation details. The common conventions are: put helper procedures in a dedicated schema and restrict permissions on that schema, prefix helper procedure names (e.g. internal_), or use naming/documentation discipline. It's a real gap compared to PL/SQL packages — T-SQL trades that encapsulation for simplicity, similar to how Python trades access modifiers for a leading-underscore convention.

✅ Quick Check

You're converting SELECT salary INTO v_salary FROM employees WHERE employee_id = 100; from PL/SQL to T-SQL. What's the correct T-SQL equivalent?

Next up: the small surprises that bite even careful developers — case sensitivity, empty strings, autocommit, and renamed date functions.

← Back to
Next →