Oracle PL/SQL Stored Procedure T-SQL · Stored Procedure

T-SQL Stored Procedure

Master T-SQL stored procedures — CREATE OR ALTER, input and OUTPUT parameters, RETURN status codes, named-parameter calls, three call forms (EXEC, EXECUTE, naked), real-world fund-transfer example with transactions, options like WITH RECOMPILE / WITH ENCRYPTION / SET NOCOUNT ON, and best practices.

What is a Stored Procedure?

A stored procedure is a named, reusable program stored inside the database. It bundles together one or more T-SQL statements — queries, DML, control flow, error handling — under a single name that you can call as needed. Procedures accept input parameters, optionally produce output parameters, and can return result sets and an integer status code.

Why Procedures Matter

Performance. SQL Server compiles a procedure's execution plan on first use and reuses it on subsequent calls — no re-parsing.
Encapsulation. Complex business logic lives in one place; callers just invoke the name.
Security. Grant EXECUTE on the procedure without granting access to the underlying tables.
Reduced network traffic. A 50-line procedure runs as a single round-trip from the application.
Atomicity. Wrap multi-statement work in BEGIN TRANSACTION ... COMMIT for all-or-nothing execution.

Syntax

CREATE [OR ALTER] PROCEDURE procedure_name @parameter1 data_type [ = default_value ] [ OUTPUT ], @parameter2 data_type [ = default_value ] [ OUTPUT ], ... AS BEGIN -- procedure body END;

💡 Use CREATE OR ALTER PROCEDURE (SQL Server 2016+). It updates the procedure if it exists, creates it if it doesn't — no need to write a DROP PROCEDURE + CREATE PROCEDURE dance. Permissions and dependencies stay intact.

Sample Tables

The examples on this page use these two tables. Run this once before the rest of the page.

SQL — Sample tables

CREATE TABLE Departments (
    department_id    INT PRIMARY KEY,
    department_name  NVARCHAR(60),
    location         VARCHAR(50)
);

CREATE TABLE Employees (
    id              INT PRIMARY KEY,
    emp_name        VARCHAR(30),
    salary          INT,
    department_id   INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id)
        REFERENCES Departments(department_id)
);

INSERT INTO Departments VALUES
    (1, 'HR',          'New York'),
    (2, 'Engineering', 'San Francisco'),
    (3, 'Marketing',   'Chicago');

INSERT INTO Employees VALUES
    (1,  'John Doe',       50000, 1),
    (2,  'Jane Smith',     60000, 2),
    (3,  'Alice Johnson',  55000, 1),
    (4,  'Bob Brown',      52000, 3),
    (5,  'Eve Brown',      48000, 2),
    (6,  'Michael Davis',  51000, 1),
    (7,  'Sarah Wilson',   59000, 3),
    (8,  'Alex Miller',    63000, 2),
    (9,  'Grace Lee',      54000, 1),
    (10, 'Samuel Moore',   57000, 3);

Examples

Example 1 — A Simple Procedure

The smallest useful procedure: no parameters, just a fixed query.

T-SQL — Create

CREATE OR ALTER PROCEDURE simple_proc
AS
BEGIN
    SELECT emp_name, salary
    FROM   Employees
    WHERE  department_id = 1;
END;
GO

Now call it. Three valid forms:

T-SQL — Call

EXEC simple_proc;            -- short form (most common)
EXECUTE simple_proc;         -- same thing, full keyword
simple_proc;                 -- only valid as the FIRST statement of a batch

OUTPUT

Results

(4 rows affected)

emp_name salary

────────────── ──────

John Doe 50000

Alice Johnson 55000

Michael Davis 51000

Grace Lee 54000

Parameter Modes

T-SQL parameters are either input (default) or output. Unlike Oracle PL/SQL, T-SQL doesn't have a separate "IN OUT" mode — but every output parameter can be passed in and read back, so it acts like one.

Mode	Marker	Direction
Input	(default — no keyword)	Caller → Procedure
Output	`OUTPUT` (or `OUT`)	Procedure → Caller (also reads any inbound value)

Example 2 — Input Parameter

T-SQL — Procedure with one input

CREATE OR ALTER PROCEDURE Employees_proc
    @Name NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;        -- suppress 'N rows affected' chatter

    SELECT emp_name, salary
    FROM   Employees
    WHERE  emp_name = @Name;
END;
GO

Call it — there are several syntax variants:

T-SQL — Call variants

EXEC     Employees_proc 'John Doe';                  -- positional
EXEC     Employees_proc @Name = 'John Doe';          -- named (recommended)
EXECUTE  Employees_proc @Name = 'John Doe';          -- same, full keyword

OUTPUT

Results

(1 row affected)

emp_name salary

───────── ──────

John Doe 50000

💡 Always prefer named-parameter calls (@Name = 'value'). They're self-documenting at the call site and don't break when someone reorders parameters in the procedure.

Example 3 — OUTPUT Parameter

OUTPUT parameters are how procedures send back values that aren't part of a result set:

T-SQL — Procedure with OUTPUT

CREATE OR ALTER PROCEDURE GetEmployeeCount
    @DepartmentID  INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM   Employees
    WHERE  department_id = @DepartmentID;
END;
GO

T-SQL — Call and read back

DECLARE @Count INT;

EXEC GetEmployeeCount
     @DepartmentID  = 1,
     @EmployeeCount = @Count OUTPUT;     -- the OUTPUT keyword is required at the call site too

SELECT @Count AS EmployeeCount;

OUTPUT

Results

(1 row affected)

EmployeeCount

─────────────

⚠️ Don't forget OUTPUT at the call site. If you write @EmployeeCount = @Count without the trailing OUTPUT, the procedure runs but @Count stays at whatever it was before — silently. The keyword is required in BOTH places.

RETURN — Status Codes

Every procedure call returns an integer status code, even if you don't write RETURN explicitly (it returns 0 by default). Use it to signal success or specific error categories — but remember it's integer-only, so it's not a substitute for OUTPUT parameters or result sets.

Example 4 — RETURN with a Computed Value

T-SQL — Procedure that returns a count

CREATE OR ALTER PROCEDURE sample_emp
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(id) FROM Employees;
    RETURN @count;       -- procedure RETURN is integer-only
END;
GO

T-SQL — Capture the return value

DECLARE @ReturnValue INT;
EXEC @ReturnValue = sample_emp;        -- captures RETURN, not OUTPUT
SELECT @ReturnValue AS EmployeeCount;

OUTPUT

Results

(1 row affected)

EmployeeCount

─────────────

📌 Convention: 0 = success, non-zero = some kind of failure. Return values larger than just 0/1 are sometimes used to encode which failure happened (1 = bad input, 2 = not found, etc.) — but for anything more complex than a status indicator, use OUTPUT parameters or raise an error with THROW.

Real-World Scenario — Fund Transfer

A practical example combining inputs, OUTPUT, conditional logic, and multiple DML statements — the kind of work procedures excel at. We'll set up two tables (accounts + transactions log) and a procedure that transfers money between accounts.

SQL — Tables

CREATE TABLE Accounts (
    account_id      INT PRIMARY KEY,
    account_number  BIGINT,
    account_holder  VARCHAR(20),
    balance         DECIMAL(18, 2)
);

INSERT INTO Accounts VALUES
    (1, 9876543210, 'Raja',   5000),
    (2, 9867657351, 'Alex',  10000),
    (3, 9864503293, 'Devid', 15000);

CREATE TABLE Transactions (
    transfer_id     INT IDENTITY(1,1) PRIMARY KEY,
    account_id      INT,
    transfer_type   VARCHAR(20),
    amount          DECIMAL(18, 2),
    transfer_date   DATETIME2 DEFAULT SYSDATETIME()
);

⚠️ Type fix from the original lesson: the original tutorial wrote account_holder VARCHAR2(20) and amount NUMBER — those are Oracle types that don't exist in SQL Server. Use VARCHAR(20) and DECIMAL / NUMERIC instead.

T-SQL — Fund-transfer procedure

CREATE OR ALTER PROCEDURE fund_transfer
    @sender_account_id    INT,
    @receiver_account_id  INT,
    @amount               DECIMAL(18, 2),
    @message              VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sender_balance DECIMAL(18, 2);

    SELECT @sender_balance = balance
    FROM   Accounts
    WHERE  account_id = @sender_account_id;

    IF @amount <= @sender_balance
    BEGIN
        BEGIN TRANSACTION;

        UPDATE Accounts
        SET    balance = balance - @amount
        WHERE  account_id = @sender_account_id;

        UPDATE Accounts
        SET    balance = balance + @amount
        WHERE  account_id = @receiver_account_id;

        INSERT INTO Transactions (account_id, transfer_type, amount)
            VALUES (@sender_account_id,   'DEBIT',  @amount);
        INSERT INTO Transactions (account_id, transfer_type, amount)
            VALUES (@receiver_account_id, 'CREDIT', @amount);

        COMMIT TRANSACTION;

        SET @message = 'TRANSFER COMPLETE';
    END
    ELSE
    BEGIN
        SET @message = 'INSUFFICIENT BALANCE';
    END
END;
GO

📌 Why BEGIN TRANSACTION? A fund transfer is two updates that must succeed together — if the debit lands but the credit fails, money disappears. Wrapping the work in an explicit transaction makes it atomic. This is the kind of business-critical reason procedures exist.

Call it for a successful transfer (Alex → Raja, ₹2000):

T-SQL — Successful transfer

DECLARE @msg VARCHAR(100);
EXEC fund_transfer
     @sender_account_id   = 2,
     @receiver_account_id = 1,
     @amount              = 2000,
     @message             = @msg OUTPUT;

SELECT @msg AS status;
SELECT * FROM Accounts;

OUTPUT

Results

(2 result sets)

status

─────────────────

TRANSFER COMPLETE

account_id account_number account_holder balance

────────── ────────────── ────────────── ────────

1 9876543210 Raja 7000.00

2 9867657351 Alex 8000.00

3 9864503293 Devid 15000.00

Now try a transfer that exceeds Alex's remaining balance:

T-SQL — Insufficient-funds case

DECLARE @msg VARCHAR(100);
EXEC fund_transfer
     @sender_account_id   = 2,
     @receiver_account_id = 1,
     @amount              = 50000,        -- way too much
     @message             = @msg OUTPUT;

SELECT @msg AS status;

OUTPUT

Results

(1 row affected)

status

─────────────────────

INSUFFICIENT BALANCE

Useful Options

Option	Effect	When to use
`WITH RECOMPILE`	Re-compiles the procedure on every call	The plan depends heavily on parameter values (parameter sniffing)
`WITH ENCRYPTION`	Stores the procedure body encrypted	To hide proprietary logic (note: not strong security — many tools can still decrypt)
`WITH EXECUTE AS`	Runs as a different user	To grant the procedure permissions the caller doesn't have
Default parameter value	`@p INT = 100`	Lets callers omit the parameter; useful for optional inputs
`SET NOCOUNT ON` at top	Suppresses "N rows affected" messages	Almost always — reduces network chatter

Dropping a Procedure

DROP PROCEDURE [IF EXISTS] procedure_name;

T-SQL — Drop

DROP PROCEDURE IF EXISTS fund_transfer;

💡 Use IF EXISTS (SQL Server 2016+) to make scripts idempotent — won't error if the procedure was already dropped.

Best Practices

Use CREATE OR ALTER instead of DROP+CREATE — preserves permissions and avoids race conditions during deployment.
Always SET NOCOUNT ON at the top — eliminates per-statement row-count messages, reducing network traffic.
Validate inputs early. Check parameters at the start; RETURN or THROW on bad input before doing real work.
Wrap multi-statement DML in transactions. Anything that touches multiple rows or tables should be atomic.
Use named-parameter calls. EXEC proc @x = 1, @y = 2 is self-documenting and reorder-safe.
Don't return non-integer values from RETURN. Use OUTPUT parameters or result sets for that.

Conclusion

A stored procedure is reusable T-SQL stored under a name — EXEC it from anywhere with permissions.
Parameters are input by default; add OUTPUT for values the procedure sends back. The keyword is required at the call site too.
RETURN sends back an integer status code; default is 0. Use OUTPUT or result sets for non-integer data.
Three call forms: positional (EXEC proc 'a', 5), named (EXEC proc @x = 'a', @y = 5), or naked (only valid as the first statement of a batch). Prefer named.
Use CREATE OR ALTER PROCEDURE for idempotent deployments and DROP PROCEDURE IF EXISTS for cleanup.
Wrap multi-statement DML in BEGIN TRANSACTION ... COMMIT — money never disappears between two updates.