Stored Procedures with Parameters and OUTPUT – SQL Server | Lesson 22

Published / updated4 Oct 2026

Introduction

When a parent pays a fee, five things must happen: check the invoice, check the amount, create a receipt number, save the payment, and update the invoice status. If any one is missed, the books are wrong.

A stored procedure is a named block of T-SQL saved in the database. The API calls it with a few values, and the procedure does all five steps, in the right order, every time.

By the end of this lesson, you will be able to:

Create a stored procedure with input parameters.
Return a value to the caller with an OUTPUT parameter.
Refuse bad input with THROW and your own error numbers.
Number receipts safely with a SEQUENCE.
Explain when to use a procedure, a function, or plain SQL from the application.

Why this matters

If the payment steps live in C# code, every application that records payments must repeat them correctly: the website, the mobile app, a bulk import. One of them will forget to update the invoice status.

With a procedure, the rules live in one place, next to the data. The procedure also lets you grant "may record payments" without granting "may change any row in FeePayments", which Lesson 28 uses.

The concept

Example: what a stored procedure is

Think of a vending machine. You press one button and put in money, and inside, the machine checks the money, drops the item, and gives change.

You never touch the parts inside. A procedure works the same way: the caller passes values, and the steps stay inside.

Example
CREATE OR ALTER PROCEDURE dbo.usp_GetClassList
    @ClassId     INT,
    @SectionName VARCHAR(2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FullName, RollNumber
    FROM dbo.vw_CurrentEnrollments
    WHERE ClassId = @ClassId
      AND SectionName = @SectionName
    ORDER BY RollNumber;
END;

Parameters such as @ClassId are inputs, each with a type.
SET NOCOUNT ON stops the "(1 rows affected)" messages, which applications do not need and which can confuse some client libraries.
EXEC dbo.usp_GetClassList @ClassId = 6, @SectionName = 'A'; runs it.

Example: OUTPUT parameters and errors

An OUTPUT parameter sends a value back to the caller, such as the new receipt number:

Example
DECLARE @Receipt VARCHAR(20);
EXEC dbo.usp_RecordFeePayment
    @FeeInvoiceId = 2,
    @Amount = 25500,
    @PaymentMode = 'Online',
    @ReceiptNumber = @Receipt OUTPUT;

THROW number, message, state stops the procedure with your own error. Error numbers 50000 and above are yours to choose. The SMS uses the numbers listed in the course reference, so the API can turn 50013 into the message "Amount is more than the balance".

Example: three ways a procedure answers

Way	How	Best for
Result set	A `SELECT` inside the procedure	Lists, such as a class list
`OUTPUT` parameter	A value written back to a variable	One value, such as a receipt number
Error	`THROW`	Anything that went wrong

There is also a RETURN value: a single whole number, usually 0 for success. Older code uses it for error codes. Prefer THROW, because an error cannot be silently ignored.

Example: SQL injection, and why parameters stop it

SQL injection is an attack where someone types SQL into a text box, and the program pastes that text straight into a query. Imagine a search box that builds this text:

Example
-- What a careless program builds when someone types:  x'; DELETE FROM dbo.Students; --
SELECT * FROM dbo.Students WHERE LastName = 'x'; DELETE FROM dbo.Students; --'

The typed text closed the quote and added a second statement. With a parameter, the whole input is treated as one value, never as code. If dynamic SQL is ever needed, pass the values as parameters of sp_executesql:

Example
DECLARE @LastName NVARCHAR(50) = N'x''; DELETE FROM dbo.Students; --';

EXEC sys.sp_executesql
    N'SELECT Id, FirstName FROM dbo.Students WHERE LastName = @LastName;',
    N'@LastName NVARCHAR(50)',
    @LastName = @LastName;

This returns no rows and deletes nothing: SQL Server looked for a student whose last name is that strange text.

Example: procedures vs functions

	Stored procedure	Function
Can change data	Yes	No
Can use transactions	Yes	No
Used inside a `SELECT`	No, called with `EXEC`	Yes
Returns	Result sets, OUTPUT values, errors	One value or one table
SMS example	Record a payment	Calculate a grade

Rule of thumb: reading and calculating fits a view or function; changing data with rules fits a procedure.

Example: a sequence for receipt numbers

A sequence is a database object that hands out numbers: 4, then 5, then 6. Two payments at the same moment always get different numbers, without any locking of FeePayments.

NEXT VALUE FOR dbo.ReceiptNumbers takes the next number. A number taken inside a transaction that is later rolled back is not given back, so receipt numbers can have gaps. Auditors accept gaps that have a reason; they do not accept two receipts with the same number.

Apply it to the SMS

Save the script below as database\022-procedures.sql and run it. It creates the sequence and both procedures.

The sequence must start after the three receipts already in the sample data. CREATE SEQUENCE only accepts a fixed number, so the script builds the statement as text and runs it with sp_executesql. This is called dynamic SQL; you will rarely need it.

Show database/022-procedures.sql

database/022-procedures.sql
-- Lesson 22: stored procedures, and a sequence for receipt numbers.
-- Safe to run more than once.
USE SchoolDb;
GO

-- Receipt numbers come from a sequence. It starts after the receipts that already exist.
-- CREATE SEQUENCE needs a fixed number, so the statement is built as text and run.
IF OBJECT_ID(N'dbo.ReceiptNumbers', N'SO') IS NULL
BEGIN
    DECLARE @StartWith BIGINT = (SELECT COUNT(*) + 1 FROM dbo.FeePayments);
    DECLARE @Sql NVARCHAR(200) = CONCAT(
        N'CREATE SEQUENCE dbo.ReceiptNumbers AS BIGINT START WITH ', @StartWith, N' INCREMENT BY 1;');
    EXEC sys.sp_executesql @Sql;
END;
GO

-- The class list for the current year.
CREATE OR ALTER PROCEDURE dbo.usp_GetClassList
    @ClassId     INT,
    @SectionName VARCHAR(2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        EnrollmentId,
        StudentId,
        AdmissionNumber,
        FullName,
        RollNumber
    FROM dbo.vw_CurrentEnrollments
    WHERE ClassId = @ClassId
      AND SectionName = @SectionName
    ORDER BY RollNumber;
END;
GO

-- Record a fee payment: check it, save it, number the receipt, update the invoice status.
-- Everything happens in one transaction: all of it is saved, or none of it.
CREATE OR ALTER PROCEDURE dbo.usp_RecordFeePayment
    @FeeInvoiceId         INT,
    @Amount               DECIMAL(10, 2),
    @PaymentMode          VARCHAR(12),
    @TransactionReference VARCHAR(50) = NULL,
    @CreatedBy            INT = NULL,
    @ReceiptNumber        VARCHAR(20) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Total DECIMAL(10, 2);
    DECLARE @Discount DECIMAL(10, 2);
    DECLARE @Status VARCHAR(13);
    DECLARE @PaidSoFar DECIMAL(10, 2);

    BEGIN TRANSACTION;

    -- UPDLOCK keeps the invoice locked until COMMIT, so two payments cannot both pass the checks.
    SELECT
        @Total = TotalAmount,
        @Discount = DiscountAmount,
        @Status = Status
    FROM dbo.FeeInvoices WITH (UPDLOCK)
    WHERE Id = @FeeInvoiceId;

    IF @Total IS NULL
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50010, 'Invoice not found.', 1;
    END;

    IF @Status = 'Cancelled'
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50011, 'Invoice is cancelled.', 1;
    END;

    IF @Amount IS NULL OR @Amount <= 0
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50012, 'Amount must be more than zero.', 1;
    END;

    SELECT @PaidSoFar = ISNULL(SUM(Amount), 0)
    FROM dbo.FeePayments
    WHERE FeeInvoiceId = @FeeInvoiceId
      AND IsDeleted = 0;

    IF @Amount > @Total - @Discount - @PaidSoFar
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50013, 'Amount is more than the balance due.', 1;
    END;

    SET @ReceiptNumber = CONCAT(
        'RCP-', YEAR(SYSUTCDATETIME()), '-',
        RIGHT(CONCAT('000000', NEXT VALUE FOR dbo.ReceiptNumbers), 6));

    INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode, TransactionReference, CreatedBy)
    VALUES (@ReceiptNumber, @FeeInvoiceId, @Amount, SYSUTCDATETIME(), @PaymentMode, @TransactionReference, @CreatedBy);

    UPDATE dbo.FeeInvoices
    SET Status = CASE WHEN @PaidSoFar + @Amount >= @Total - @Discount THEN 'Paid' ELSE 'PartiallyPaid' END,
        UpdatedAt = SYSUTCDATETIME(),
        UpdatedBy = @CreatedBy
    WHERE Id = @FeeInvoiceId;

    COMMIT TRANSACTION;
END;
GO

-- Check: the procedures and the sequence.
SELECT name AS ObjectName, type_desc AS ObjectType
FROM sys.objects
WHERE name IN ('usp_GetClassList', 'usp_RecordFeePayment', 'ReceiptNumbers')
ORDER BY name;
GO

Example: inside the payment procedure

Read usp_RecordFeePayment in order:

Step	What it does	If it fails
1	Starts a transaction and reads the invoice with `UPDLOCK`
2	Checks the invoice exists	`THROW 50010`
3	Checks it is not cancelled	`THROW 50011`
4	Checks the amount is above zero	`THROW 50012`
5	Adds up earlier payments and checks the balance	`THROW 50013`
6	Takes the next receipt number from the sequence
7	Inserts the payment
8	Sets the invoice to Paid or PartiallyPaid
9	Commits

UPDLOCK keeps the invoice locked until the commit, so two payments for the same invoice cannot both pass the balance check at the same moment. Lesson 25 explains locks. SET XACT_ABORT ON makes any unexpected error undo the whole transaction (Lesson 24).

Example: calling the procedures

The demo script below runs three calls. The payment test runs inside a transaction that is rolled back, so no data changes.

Show queries/022-procedures.sql

queries/022-procedures.sql
-- Lesson 22: calling stored procedures.
-- The payment tests run inside a transaction that is rolled back, so no data changes.
-- (A sequence number used inside a rolled-back transaction is not given back.)
USE SchoolDb;
GO

-- 1. A procedure with parameters: the 6-A class list.
EXEC dbo.usp_GetClassList @ClassId = 6, @SectionName = 'A';

-- 2. A procedure with an OUTPUT parameter: Diya pays her remaining 25,500 (invoice 2).
BEGIN TRANSACTION;

DECLARE @Receipt VARCHAR(20);

EXEC dbo.usp_RecordFeePayment
    @FeeInvoiceId = 2,
    @Amount = 25500,
    @PaymentMode = 'Online',
    @TransactionReference = 'TXN-90011223',
    @ReceiptNumber = @Receipt OUTPUT;

SELECT @Receipt AS ReceiptNumber;

SELECT InvoiceNumber, Status
FROM dbo.FeeInvoices
WHERE Id = 2;

ROLLBACK TRANSACTION;
GO

-- 3. The procedure refuses a payment above the balance (Kabir's invoice 3 is 45,500).
DECLARE @Receipt VARCHAR(20);

EXEC dbo.usp_RecordFeePayment
    @FeeInvoiceId = 3,
    @Amount = 50000,
    @PaymentMode = 'Cash',
    @ReceiptNumber = @Receipt OUTPUT;
GO

Call 1, the 6-A class list:

EnrollmentId	StudentId	AdmissionNumber	FullName	RollNumber
19	7	ADM-2021-0001	Advika Sharma	1
20	8	ADM-2021-0002	Ananya Reddy	2
21	9	ADM-2021-0003	Vihaan Kapoor	3
22	10	ADM-2021-0004	Zara Ali	4

Call 2, Diya pays her remaining 25,500. The procedure returns the receipt number, and the invoice becomes Paid:

ReceiptNumber
RCP-2026-000004

InvoiceNumber	Status
INV-2026-000002	Paid

The receipt number uses the current year and the next sequence value. The first run on a fresh database gives RCP-2026-000004.

Run the script again and you get 000005: the rolled-back number was used up. That is the sequence gap you read about.

Call 3, a payment of 50,000 on Kabir's invoice of 45,500:

Output
Msg 50013, Level 16, State 1, Procedure dbo.usp_RecordFeePayment, Line 57
Amount is more than the balance due.

The procedure rolled back before saving anything. After the script, there are still exactly three payments, and Diya's invoice is still PartiallyPaid.

Example: calling it from the API

In Module 9, the C# code calls this procedure with Dapper in a few lines: the procedure name, the four input values, and an output parameter for the receipt number. The API never builds payment SQL itself.

If the procedure throws 50013, the API catches it and answers the parent's app with status 422 and the message "Amount is more than the balance due" (Lesson 74). Lesson 38 also checks the same rule in C#, with the same error number, so every screen gets the same message.

Example: changing a procedure safely

CREATE OR ALTER PROCEDURE replaces the code but keeps the permissions granted on the procedure. Dropping and re-creating it would lose them.

Because the API depends on the parameter names and the result columns, treat them as a contract. Add new optional parameters at the end, with a default value, and older callers keep working.

Common mistakes

Building SQL by joining strings with user input. It allows SQL injection. Fix: use parameters; if dynamic SQL is unavoidable, pass values through sp_executesql parameters.
Returning error codes nobody checks. A silent RETURN 1 is easy to ignore. Fix: THROW an error the caller cannot miss.
Checking the balance without a lock. Two payments can both pass. Fix: lock the invoice row with UPDLOCK inside the transaction.
Using MAX(...) + 1 for numbers. Two sessions get the same number. Fix: use a SEQUENCE or IDENTITY.
Procedures that do everything. One procedure with 20 optional parameters becomes impossible to test. Fix: one procedure per business action.

In real projects

From 16 years of .NET projects

On finance features, I keep the "money moves" logic in one stored procedure, exactly like usp_RecordFeePayment. Every channel, from the web page to the bulk bank-file import, calls the same procedure, so the rules cannot drift apart.

I have also seen the opposite extreme: 400 procedures containing all business logic, with no tests and nobody brave enough to change them. My balance today is clear: procedures for data-heavy, transactional steps; C# for workflows, validation messages, and anything that needs unit tests. You will see both sides in Modules 9 to 12.

Best practices

Name procedures usp_VerbNoun, such as usp_RecordFeePayment.
Start every procedure with SET NOCOUNT ON, and use SET XACT_ABORT ON when it changes data.
Validate inputs first and THROW clear errors with documented numbers.
Use SEQUENCE or IDENTITY for numbers, never MAX + 1.
Keep one business action per procedure.

Recap

A stored procedure is named T-SQL that takes parameters and can change data.
OUTPUT parameters return values; THROW returns errors.
A sequence hands out unique numbers; rolled-back numbers leave gaps.
usp_RecordFeePayment checks, saves, numbers, and updates in one transaction.
Procedures change data with rules; functions and views read and calculate.
Parameters, not string building, protect queries from SQL injection.

Homework

Library module task: Write usp_IssueBook, which issues one copy of a book to a student.

Take a copy, a student, and an issue date.
Refuse the copy if it is not available (error 50020).
Save the issue with a due date 14 days later.
Return the new issue's Id in an OUTPUT parameter.

Do not change the copy's status here; Lesson 23's trigger will.

A correct result looks like: issuing an available copy returns an Id; issuing an issued copy fails with "This copy is not available."

Show the solution

library-assignment/022-library-issue-procedure.sql
-- Library homework, Lesson 22: a procedure that issues a copy for 14 days.
-- The copy's status is kept in sync by the Lesson 23 trigger, not here.
USE SchoolDb;
GO

CREATE OR ALTER PROCEDURE dbo.usp_IssueBook
    @BookCopyId  INT,
    @StudentId   INT,
    @IssuedOn    DATE,
    @BookIssueId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    IF NOT EXISTS (
        SELECT 1
        FROM dbo.BookCopies WITH (UPDLOCK)
        WHERE Id = @BookCopyId
          AND Status = 'Available'
    )
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50020, 'This copy is not available.', 1;
    END;

    INSERT INTO dbo.BookIssues (BookCopyId, StudentId, IssuedOn, DueOn)
    VALUES (@BookCopyId, @StudentId, @IssuedOn, DATEADD(DAY, 14, @IssuedOn));

    SET @BookIssueId = SCOPE_IDENTITY();

    COMMIT TRANSACTION;
END;
GO

SCOPE_IDENTITY() returns the IDENTITY value just created in this procedure.

Quiz

What can a stored procedure do that a function cannot?
1. Return a value
2. Change data and use transactions
3. Take parameters
4. Be called in SELECT
Show answer
Answer: B. Change data and use transactions Functions cannot change data; procedures can, inside transactions.
How does usp_RecordFeePayment give the receipt number back to the caller?
1. With PRINT
2. With an OUTPUT parameter
3. With a global variable
4. By changing the invoice number
Show answer
Answer: B. With an OUTPUT parameter @ReceiptNumber is declared OUTPUT and passed with the OUTPUT keyword.
Why can receipt numbers from a sequence have gaps?
1. The sequence is broken
2. Numbers used in rolled-back transactions are not returned
3. SQL Server skips even numbers
4. Gaps happen only on Express edition
Show answer
Answer: B. Numbers used in rolled-back transactions are not returned A sequence never hands a number out twice, even if the transaction rolls back.
What does THROW 50013, 'Amount is more than the balance due.', 1 do?
1. Logs a warning and continues
2. Stops with that error number and message
3. Retries the payment
4. Deletes the invoice
Show answer
Answer: B. Stops with that error number and message THROW raises an error that ends the procedure; the caller receives number and message.
Why does the procedure read the invoice WITH (UPDLOCK)?
1. To make it faster
2. So two payments cannot both pass the balance check at once
3. To hide the invoice
4. Because SELECT requires it
Show answer
Answer: B. So two payments cannot both pass the balance check at once The lock is held until COMMIT, so the second payment waits and sees the first.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a stored procedure? concept

A stored procedure is a named block of T-SQL saved in the database, with parameters. It can read and change data and use transactions. The SMS uses usp_RecordFeePayment to check, save, number, and update a payment in one call.

See the example · Permalink

F What is the difference between a stored procedure and a function? concept

A procedure can change data and use transactions, and is called with EXEC. A function cannot change data, and is used inside a query, returning one value or a table. Use procedures for business actions and functions for calculations and reusable reads.

See the example · Permalink

F What is an OUTPUT parameter? concept

A parameter that sends a value back to the caller. usp_RecordFeePayment returns the new receipt number through @ReceiptNumber OUTPUT, and the caller passes a variable with the OUTPUT keyword to receive it.

See the example · Permalink

Mid (3-5 yrs)

M Why use SET NOCOUNT ON in a stored procedure? concept

It stops SQL Server from sending a "rows affected" message after every statement. The application does not need them, they add network traffic, and some client libraries can confuse them with result sets.

See the example · Permalink

M How do stored procedures protect against SQL injection, and when can they still be vulnerable? concept

Parameters are treated as values, never as code, so input like '; DELETE ... is just text. A procedure is still vulnerable if it builds dynamic SQL by joining strings with input. Dynamic SQL must pass values through sp_executesql parameters.

See the example · Permalink

M Why use a SEQUENCE instead of MAX(...) + 1 to number receipts? concept

Two sessions running MAX + 1 at the same time can read the same maximum and produce the same number. A sequence hands out each number exactly once, without locking the table. Numbers used in rolled-back transactions are not returned, so gaps can appear.

See the example · Permalink

Senior (6-9 yrs)

S Two payments for the same invoice arrive at the same moment. How does usp_RecordFeePayment stay correct? scenario

It reads the invoice WITH (UPDLOCK) inside its transaction. The second payment waits until the first commits, then sees the updated balance, so both cannot pass the balance check. The receipt numbers come from a sequence, so they never collide.

How to answer: Describe the race condition first (both read the same balance), then the fix (lock the row you will change, early), and the guarantee it gives. Mention the cost: payments for the same invoice are serialized, which is acceptable because they are rare.

See the example · Permalink

S How do you change a stored procedure that the API depends on without breaking it? design

Treat parameters and result columns as a contract. Use CREATE OR ALTER so permissions are kept, add new parameters at the end with defaults, and do not rename or remove result columns that callers read. Deploy the database change before the application change that uses it.

How to answer: Explain backward compatibility, the deployment order, and how you test both old and new callers. Mention versioned procedure names as a last resort for breaking changes.

See the example · Permalink

Architect / Lead (10+ yrs)

A Should business logic live in stored procedures or in application code? design

Data-heavy, transactional steps that must be atomic, such as recording a payment, fit procedures well. Workflows, validation messages, integrations, and logic that needs unit tests fit application code better. Most systems use both, with a clear rule for which goes where.

How to answer: Avoid absolutes. Weigh testability, deployment, team skills, performance, and reuse across channels. Give SMS examples on both sides and describe the agreed boundary your team would write down.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-22-stored-procedures (code repository coming soon)
Files: database/022-procedures.sql, queries/022-procedures.sql, library-assignment/022-library-issue-procedure.sql
Video: coming soon

What's next

Procedures run when called. Triggers run by themselves whenever data changes, which is how the marks rule from Lesson 13 is finally enforced. Lesson 23: Triggers

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: what a stored procedure is​

Example: OUTPUT parameters and errors​

Example: three ways a procedure answers​

Example: SQL injection, and why parameters stop it​

Example: procedures vs functions​

Example: a sequence for receipt numbers​

Apply it to the SMS​

Example: inside the payment procedure​

Example: calling the procedures​

Example: calling it from the API​

Example: changing a procedure safely​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: what a stored procedure is

Example: OUTPUT parameters and errors

Example: three ways a procedure answers

Example: SQL injection, and why parameters stop it

Example: procedures vs functions

Example: a sequence for receipt numbers

Apply it to the SMS

Example: inside the payment procedure

Example: calling the procedures

Example: calling it from the API

Example: changing a procedure safely

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next