Published / updated29 Aug 2026

Views and Stored Procedures

Before you start

You need: joins (Article 06) and grouping (Article 07).

Time: about 55 minutes, plus the practice. Procedures are what the application calls in Track 07.

Learning objective

Write a stored procedure an application can call safely, with correct parameters, error handling and return values.

Topics

• Views — what they are and are not
• View limitations
• Creating and altering procedures
• Input, output and default parameters
• SET NOCOUNT ON and why it matters
• TRY...CATCH and THROW
• Return values versus output parameters
• Table-valued parameters
• Dynamic SQL, and how to make it safe

Views

A view is a stored SELECT. It behaves like a table when queried, but stores no data.

CREATE VIEW dbo.vw_StudentFeeSummary
AS
SELECT   s.SchoolId,
         s.Id            AS StudentId,
         s.PublicId,
         s.RollNumber,
         s.Name,
         s.ClassName,
         s.Section,
         fa.AcademicYear,
         fa.TotalFees,
         fa.DiscountAmount,
         ISNULL(paid.TotalPaid, 0)                                       AS TotalPaid,
         fa.TotalFees - fa.DiscountAmount - ISNULL(paid.TotalPaid, 0)    AS Outstanding
FROM     dbo.Student    AS s
JOIN     dbo.FeeAccount AS fa ON fa.StudentId = s.Id
LEFT JOIN (
             SELECT   p.FeeAccountId, SUM(p.Amount) AS TotalPaid
             FROM     dbo.FeePayment AS p
             WHERE    p.IsCancelled = 0
             GROUP BY p.FeeAccountId
         ) AS paid ON paid.FeeAccountId = fa.Id
WHERE    s.Status <> 1;
GO

SELECT RollNumber, Name, Outstanding
FROM   dbo.vw_StudentFeeSummary
WHERE  SchoolId = 1 AND AcademicYear = '2024-25' AND Outstanding > 0
ORDER BY Outstanding DESC;

Views are good for three things: hiding join complexity so reports are simple to write, presenting a stable shape while the underlying tables change, and restricting column access — grant SELECT on the view without granting it on the base table.

What a view is not

Not a performance feature. A standard view is expanded into the calling query at run time. Selecting from it is exactly as expensive as writing the query out. (An indexed view does materialise, but it carries heavy restrictions and slows every write to the base tables.)

Not parameterised. A view cannot take arguments. SchoolId must be filtered by the caller, which is why it appears in the view's column list above rather than being hardcoded.

Do not nest views deeply. A view selecting from a view selecting from a view produces a query plan nobody can read, and one small change at the bottom degrades everything above it. Two levels is a reasonable limit.

View limitations

• No ORDER BY without TOP — and even then the order is not guaranteed to the caller. Sort in the query that uses the view.
• Every column needs a name. Computed columns require an alias.
• SELECT * in a view is a trap: the column list is fixed when the view is created, so a column added to the base table does not appear until you run sp_refreshview or recreate it. Name your columns.

Updating through a view is possible but limited to a single base table with no aggregates. In practice, write to tables and read from views.

Stored procedures

CREATE OR ALTER PROCEDURE dbo.usp_GetStudentsByClass
    @SchoolId  INT,
    @ClassName NVARCHAR(10),
    @Section   NVARCHAR(1) = NULL      -- optional
AS
BEGIN
    SET NOCOUNT ON;

    SELECT   s.Id,
             s.PublicId,
             s.RollNumber,
             s.Name,
             s.ClassName,
             s.Section,
             s.ParentPhone
    FROM     dbo.Student AS s
    WHERE    s.SchoolId  = @SchoolId
      AND    s.ClassName = @ClassName
      AND    (@Section IS NULL OR s.Section = @Section)
      AND    s.Status <> 1
    ORDER BY s.Section, s.Name;
END;
GO

EXEC dbo.usp_GetStudentsByClass @SchoolId = 1, @ClassName = '10th';
EXEC dbo.usp_GetStudentsByClass @SchoolId = 1, @ClassName = '10th', @Section = 'A';

CREATE OR ALTER (SQL Server 2016 SP1 and later) replaces the old IF EXISTS ... DROP ... CREATE pattern, and unlike DROP/CREATE it preserves permissions granted on the procedure.

Always pass parameters by name. EXEC dbo.usp_GetStudentsByClass 1, '10th' works until someone adds a parameter in the middle.

SET NOCOUNT ON

Suppresses the "(15 rows affected)" message. Two reasons it belongs in every procedure:

• Those messages are extra network round trips — measurable on a procedure called thousands of times a minute.
• Some data-access layers treat them as additional result sets, so a reader ends up on the wrong one.

The (@Section IS NULL OR s.Section = @Section) pattern handles an optional filter in one query. It is readable, and for small tables it is fine. On large tables it can produce a plan optimised for one parameter combination and reused badly for another — add OPTION (RECOMPILE) if that becomes a problem.

Why procedures

Benefit	Detail
Parameterised by construction	Injection is much harder to write by accident
One place for logic	Several applications share the same rules
Permission boundary	Grant EXECUTE on the procedure without SELECT on the tables
Changeable without redeploying	Fix a query without rebuilding the application
Plan reuse	The plan is cached and reused

The permission point is the strongest. An application login with EXECUTE on procedures but no table access cannot be made to read the User table, even through a bug.

Full CRUD

CREATE OR ALTER PROCEDURE dbo.usp_CreateStudent
    @SchoolId    INT,
    @Name        NVARCHAR(100),
    @RollNumber  NVARCHAR(20),
    @ClassName   NVARCHAR(10),
    @Section     NVARCHAR(1),
    @DateOfBirth DATE,
    @ParentName  NVARCHAR(100),
    @ParentPhone NVARCHAR(15),
    @Address     NVARCHAR(250) = NULL,
    @NewId       INT           OUTPUT,
    @NewPublicId UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Student
               WHERE SchoolId = @SchoolId AND RollNumber = @RollNumber)
    BEGIN
        THROW 50001, 'This roll number is already used by another student.', 1;
    END

    SET @NewPublicId = NEWID();

    INSERT INTO dbo.Student (PublicId, SchoolId, Name, RollNumber, ClassName, Section,
                             DateOfBirth, ParentName, ParentPhone, Address, Status)
    VALUES (@NewPublicId, @SchoolId, @Name, @RollNumber, @ClassName, @Section,
            @DateOfBirth, @ParentName, @ParentPhone, @Address, 0);

    SET @NewId = SCOPE_IDENTITY();
END;
GO

DECLARE @id INT, @publicId UNIQUEIDENTIFIER;

EXEC dbo.usp_CreateStudent
     @SchoolId    = 1,
     @Name        = N'Sneha Patel',
     @RollNumber  = 'NCA-2024-0044',
     @ClassName   = '9th',
     @Section     = 'A',
     @DateOfBirth = '2010-03-14',
     @ParentName  = N'Mahesh Patel',
     @ParentPhone = '9812345670',
     @NewId       = @id       OUTPUT,
     @NewPublicId = @publicId OUTPUT;

SELECT @id AS NewId, @publicId AS NewPublicId;

The OUTPUT keyword is required in both the declaration and the call. Omitting it on the call is a common error — the procedure runs, and the caller's variable stays NULL with no warning.

Note the duplicate check and the unique constraint coexist. The check gives a clean message; the constraint wins the race when two callers insert simultaneously.

Update and delete

CREATE OR ALTER PROCEDURE dbo.usp_UpdateStudent
    @SchoolId    INT,
    @PublicId    UNIQUEIDENTIFIER,
    @Name        NVARCHAR(100),
    @RollNumber  NVARCHAR(20),
    @ClassName   NVARCHAR(10),
    @Section     NVARCHAR(1),
    @ParentPhone NVARCHAR(15)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Student
               WHERE SchoolId = @SchoolId
                 AND RollNumber = @RollNumber
                 AND PublicId <> @PublicId)      -- exclude this student
    BEGIN
        THROW 50001, 'This roll number is already used by another student.', 1;
    END

    UPDATE dbo.Student
    SET    Name        = @Name,
           RollNumber  = @RollNumber,
           ClassName   = @ClassName,
           Section     = @Section,
           ParentPhone = @ParentPhone
    WHERE  SchoolId = @SchoolId
      AND  PublicId = @PublicId;

    IF @@ROWCOUNT = 0
    BEGIN
        THROW 50002, 'Student not found.', 1;
    END
END;
GO

Two things this gets right. PublicId <> @PublicId excludes the student from their own duplicate check — without it, saving an unchanged roll number reports a conflict with itself. And SchoolId is in the WHERE clause of the UPDATE, so a leaked PublicId cannot modify another school's record.

The @@ROWCOUNT check turns "matched nothing" into an error the caller can act on, rather than a silent success.

CREATE OR ALTER PROCEDURE dbo.usp_DeactivateStudent
    @SchoolId INT,
    @PublicId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Student
    SET    Status = 1
    WHERE  SchoolId = @SchoolId AND PublicId = @PublicId;

    IF @@ROWCOUNT = 0
    BEGIN
        THROW 50002, 'Student not found.', 1;
    END
END;
GO

Soft delete, because the student has exam, attendance and payment history.

Error handling

CREATE OR ALTER PROCEDURE dbo.usp_RecordFeePayment
    @SchoolId     INT,
    @FeeAccountId INT,
    @Amount       DECIMAL(18,2),
    @PaymentMode  TINYINT,
    @CollectedBy  NVARCHAR(100),
    @NewPaymentId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        IF @Amount <= 0
        BEGIN
            THROW 50010, 'Payment amount must be greater than zero.', 1;
        END

        DECLARE @outstanding DECIMAL(18,2);

        SELECT @outstanding = fa.TotalFees - fa.DiscountAmount - fa.PaidAmount
        FROM   dbo.FeeAccount AS fa
        WHERE  fa.Id = @FeeAccountId AND fa.SchoolId = @SchoolId;

        IF @outstanding IS NULL
        BEGIN
            THROW 50011, 'Fee account not found.', 1;
        END

        IF @Amount > @outstanding
        BEGIN
            THROW 50012, 'Payment exceeds the outstanding balance.', 1;
        END

        BEGIN TRANSACTION;

        INSERT INTO dbo.FeePayment (PublicId, SchoolId, FeeAccountId, Amount,
                                    PaidOn, PaymentMode, CollectedBy)
        VALUES (NEWID(), @SchoolId, @FeeAccountId, @Amount,
                SYSUTCDATETIME(), @PaymentMode, @CollectedBy);

        SET @NewPaymentId = SCOPE_IDENTITY();

        UPDATE dbo.FeeAccount
        SET    PaidAmount = PaidAmount + @Amount
        WHERE  Id = @FeeAccountId AND SchoolId = @SchoolId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        THROW;   -- rethrow, preserving the original error
    END CATCH
END;
GO

Points worth taking from this:

• SET XACT_ABORT ON ensures the transaction is rolled back on any error, including ones TRY...CATCH cannot intercept, such as a lock timeout.
• IF @@TRANCOUNT > 0 before ROLLBACK. Rolling back when no transaction is open is itself an error.
• Bare THROW rethrows the original error with its number, message and line. THROW 50000, ERROR_MESSAGE(), 1 would replace them and lose the detail.
• The insert and the balance update are one transaction. Recording a payment without updating the balance leaves the account permanently wrong.
• Validation happens before BEGIN TRANSACTION, so a rejected payment never opens one.

THROW requires an error number of 50000 or above for user-defined errors. The older RAISERROR is still common in legacy code; THROW is simpler and rethrows correctly.

Reading error details

BEGIN CATCH
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_MESSAGE()   AS ErrorMessage,
           ERROR_SEVERITY()  AS Severity,
           ERROR_LINE()      AS ErrorLine,
           ERROR_PROCEDURE() AS ProcedureName;
END CATCH

Useful for logging inside the CATCH before rethrowing.

Return values versus output parameters

CREATE OR ALTER PROCEDURE dbo.usp_CheckRollNumber
    @SchoolId   INT,
    @RollNumber NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Student
               WHERE SchoolId = @SchoolId AND RollNumber = @RollNumber)
    BEGIN
        RETURN 1;
    END

    RETURN 0;
END;
GO

RETURN carries a single INT and is conventionally a status code only. Use OUTPUT parameters for data — a return value cannot hold a decimal, a string, or a GUID, and many data-access layers ignore it by default.

Table-valued parameters

Passing a set into a procedure, instead of calling it once per row:

CREATE TYPE dbo.ExamResultList AS TABLE
(
    StudentId     INT           NOT NULL,
    MarksObtained DECIMAL(5,2)  NULL,
    IsAbsent      BIT           NOT NULL
);
GO

CREATE OR ALTER PROCEDURE dbo.usp_SaveExamResults
    @SchoolId INT,
    @ExamId   INT,
    @Results  dbo.ExamResultList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    MERGE dbo.ExamResult AS target
    USING @Results       AS source
       ON target.ExamId = @ExamId AND target.StudentId = source.StudentId
    WHEN MATCHED THEN
        UPDATE SET MarksObtained = source.MarksObtained,
                   IsAbsent      = source.IsAbsent
    WHEN NOT MATCHED THEN
        INSERT (PublicId, SchoolId, ExamId, StudentId, MarksObtained, IsAbsent)
        VALUES (NEWID(), @SchoolId, @ExamId, source.StudentId,
                source.MarksObtained, source.IsAbsent);

    COMMIT TRANSACTION;
END;
GO

A table-valued parameter must be READONLY. Saving 40 results in one call rather than 40 round trips is a large difference over a network.

Dynamic SQL

Sometimes unavoidable — a sort column chosen at run time, for instance.

-- NEVER do this: direct injection route
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT * FROM dbo.Student WHERE Name LIKE ''%' + @search + N'%''';
EXEC (@sql);

-- Correct: parameterised, with the sort column whitelisted
CREATE OR ALTER PROCEDURE dbo.usp_SearchStudents
    @SchoolId   INT,
    @Search     NVARCHAR(100),
    @SortColumn NVARCHAR(30) = 'Name'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @orderBy NVARCHAR(60);

    SET @orderBy =
        CASE @SortColumn
            WHEN 'Name'       THEN N'Name'
            WHEN 'RollNumber' THEN N'RollNumber'
            WHEN 'ClassName'  THEN N'ClassName, Section'
            ELSE                   N'Name'
        END;

    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT   s.Id, s.PublicId, s.RollNumber, s.Name, s.ClassName, s.Section
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId
          AND    s.Status <> 1
          AND    (s.Name LIKE @Like OR s.RollNumber LIKE @Like)
        ORDER BY ' + @orderBy + N', s.Id;';

    EXEC sp_executesql @sql,
         N'@SchoolId INT, @Like NVARCHAR(102)',
         @SchoolId = @SchoolId,
         @Like     = @Search;
END;
GO

Two rules for dynamic SQL:

• Values are parameters, passed through sp_executesql, never concatenated.
• Identifiers cannot be parameters, so a column or table name chosen at run time must come from a whitelist — the CASE above — never from the caller's string. QUOTENAME() is the alternative when the set of names is not fixed.

Errors you will hit

Message	Cause	Fix
Must declare the scalar variable '@SchoolId'	Parameter used but never declared or passed	Declare it in the signature and pass it
Procedure or function expects parameter which was not supplied	Caller missed an argument	Give it a default, or pass it
There is already an object named 'usp_X'	Creating one that exists	CREATE OR ALTER PROCEDURE
Incorrect syntax near 'GO'	GO inside a procedure body	GO is a batch separator, not T-SQL
A view does not show a new column	Views cache their shape	sp_refreshview, or recreate it
The procedure returns nothing	The caller passed a SchoolId with no rows	Check with a direct SELECT

CREATE OR ALTER PROCEDURE is the form to use. It works whether or not the procedure exists, so the same script runs on a fresh database and an existing one.

Common mistakes

• SELECT * in a view, so new base-table columns never appear
• Deeply nested views
• Expecting a view to improve performance
• Omitting SET NOCOUNT ON
• Forgetting OUTPUT on the call, leaving the caller's variable NULL
• ROLLBACK without checking @@TRANCOUNT
• THROW 50000, ERROR_MESSAGE(), 1 instead of a bare THROW
• No SET XACT_ABORT ON alongside a transaction
• Validation inside the transaction rather than before it
• RETURN used to pass data instead of a status
• Positional EXEC arguments
• Concatenating values into dynamic SQL
• A duplicate check with no matching unique constraint
• Omitting SchoolId from the WHERE clause of an update

Practice

The course exercise is create parameterised procedures; the assignments are a reporting view and CRUD procedures.

1. Create vw_StudentFeeSummary and query it for students owing money. Confirm it exposes SchoolId so the caller can filter.
2. Add a column to FeeAccount, then confirm whether it appears in a view written with SELECT * versus a named column list.
3. Write usp_GetStudentsByClass with an optional @Section. Call it both ways.
4. Write usp_CreateStudent with @NewId and @NewPublicId output parameters. Call it without the OUTPUT keyword and record what the caller receives.
5. Write usp_UpdateStudent with the self-excluding duplicate check. Save a student without changing the roll number and confirm no false conflict.
6. Write usp_RecordFeePayment with full TRY...CATCH, XACT_ABORT, and one transaction covering both statements.
7. Test it: a zero amount, an amount above the balance, a non-existent account, and a valid payment. Confirm the balance is right after each.
8. Force a failure between the insert and the update — add a temporary THROW after the insert — and confirm the payment is rolled back and the balance unchanged.
9. Write usp_SearchStudents with a whitelisted sort column. Then pass 'Name; DROP TABLE Student--' as @SortColumn and confirm it falls through to the default.
10. Create the ExamResultList type and usp_SaveExamResults, and save 40 results in one call.

Exercise 8 is the point of the whole article: a transaction that is not rolled back leaves a payment recorded against an unchanged balance, and nobody notices until an audit.

Then run the course debugging exercise — correct a procedure parameter mismatch. Call a procedure with a missing required parameter, with parameters in the wrong positional order, and with @Section passed as a 5-character string against NVARCHAR(1). Record the exact error, or the silent truncation, for each.

You can now

• Create a reporting view and say what it hides
• Write a parameterised stored procedure with input and output parameters
• Use CREATE OR ALTER so scripts are re-runnable
• Take @SchoolId as a parameter on every procedure
• Say why GO cannot appear inside a procedure body

Review questions

1. Why is a standard view not a performance optimisation?
2. Why must ROLLBACK be guarded by IF @@TRANCOUNT > 0?
3. What does a bare THROW preserve that THROW 50000, ERROR_MESSAGE(), 1 loses?
4. How do you allow a run-time sort column without opening a SQL injection route?

Next: Transactions, indexes and design