CT004-3.5-3 Advanced Database Systems · Week 5

Implementing Stored Procedures

What stored procedures are, why they are used, and how to create, alter, drop and parameterize them in T-SQL.

Contents

Learning outcomes

Implement stored procedures
Create parameterized stored procedures

Examples use Microsoft SQL Server (Transact-SQL) and the AdventureWorks sample database.

What is a stored procedure?

Definition

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan and stored in the database under a name.

Stored procedures can:

contain statements that perform operations (queries, inserts, updates, control flow…);
accept input parameters;
return a status value to indicate success or failure;
return multiple output parameters.

Extra · analogy

Think of a stored procedure as a function/method that lives inside the database. The application calls it by name (EXEC AddDepartment …) instead of sending the raw SQL each time.

Advantages of stored procedures

Advantage	Explanation
Share application logic	All clients use the same procedures, ensuring consistent data access and modification.
Shield database schema details	Users never need to access the tables directly; the schema can change without breaking callers.
Provide security mechanisms	Users can be granted permission to EXECUTE a procedure even if they have no permission on the underlying tables or views.
Improve performance	The statements become part of a single execution plan on the server, which is compiled once and reused.
Reduce network traffic	Instead of sending hundreds of statements over the network, the client sends one EXEC statement, reducing client–server round trips.

Exam tip

A popular question is “explain three advantages of stored procedures”. Pick security, performance and reduced network traffic, and give a concrete example for each (e.g. granting EXECUTE on AddDepartment without granting INSERT on Department).

Creating and executing

Create in the current database with CREATE PROCEDURE; run with EXECUTE (or EXEC).

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ ,...n ]
AS
    { <sql_statement> [;] } [ ...n ]

EXECUTE [schema_name.] procedure_name

Example

CREATE PROCEDURE Production.LongLeadProducts
AS
    SELECT Name, ProductNumber
    FROM   Production.Product
    WHERE  DaysToManufacture >= 1
GO

EXECUTE Production.LongLeadProducts

This returns the name and product number of every product that takes at least one day to manufacture.

Extra · GO

GO is not T-SQL; it is a batch separator understood by SSMS/sqlcmd. CREATE PROCEDURE must be the first statement in its batch, so a GO ends the procedure body before the EXECUTE.

Guidelines for creating stored procedures

✅ Qualify object names inside the procedure (e.g. Production.Product, not just Product).
✅ Create one stored procedure for one task.
✅ Create, test, and troubleshoot (on the server, before deploying).
✅ Avoid the sp_ prefix in stored procedure names.

Extra · why avoid sp_?

SQL Server reserves sp_ for system procedures and looks for them in the master database first. A user procedure named sp_… causes an extra lookup (slower) and could be shadowed by a future system procedure of the same name.

Altering and dropping

ALTER PROC Production.LongLeadProducts
AS
    SELECT   Name, ProductNumber, DaysToManufacture
    FROM     Production.Product
    WHERE    DaysToManufacture >= 1
    ORDER BY DaysToManufacture DESC, Name
GO

DROP PROC Production.LongLeadProducts

ALTER PROCEDURE replaces the definition while keeping existing permissions (unlike DROP + CREATE). DROP PROCEDURE removes it completely.

Parameterized stored procedures

Parameterized stored procedures have three major components:

Component	Direction	Details
Input parameters	Caller → procedure	Allow the caller to pass a data value to the procedure. Declared as variables in `CREATE PROCEDURE`.
Output parameters	Procedure → caller	Allow the procedure to pass a data value (or cursor) back. The `OUTPUT` keyword is required in both CREATE PROCEDURE and EXECUTE. (User-defined functions cannot have output parameters.)
Return values	Procedure → caller	Every procedure returns an integer return code. If not explicitly set, it is 0. Most commonly used for a status/error code via `RETURN`.

Input parameters

Best practices:

Provide appropriate default values.
Validate incoming parameter values, including null checks.

ALTER PROC Production.LongLeadProducts
    @MinimumLength int = 1              -- default value
AS
    IF (@MinimumLength < 0)             -- validate
    BEGIN
        RAISERROR('Invalid lead time.', 14, 1)
        RETURN
    END

    SELECT   Name, ProductNumber, DaysToManufacture
    FROM     Production.Product
    WHERE    DaysToManufacture >= @MinimumLength
    ORDER BY DaysToManufacture DESC, Name
GO

EXEC Production.LongLeadProducts @MinimumLength = 4   -- named parameter
EXEC Production.LongLeadProducts                      -- uses default 1

Extra · RAISERROR(message, severity, state)

Severity 11–16 = user-correctable errors (returned to the client as an error). State is an arbitrary number (1–255) you can use to identify where the error was raised. RETURN then exits the procedure immediately.

Output parameters and return values

Output parameter

CREATE PROC HumanResources.AddDepartment
    @Name      nvarchar(50),
    @GroupName nvarchar(50),
    @DeptID    smallint OUTPUT
AS
    INSERT INTO HumanResources.Department (Name, GroupName)
    VALUES (@Name, @GroupName)

    SET @DeptID = SCOPE_IDENTITY()      -- new identity value
GO

DECLARE @dept int
EXEC AddDepartment 'Refunds', '', @dept OUTPUT
SELECT @dept

SCOPE_IDENTITY() returns the last identity value generated in the current scope — i.e. the new department’s ID — which is passed back through @DeptID into the caller’s @dept.

Adding a return value

ALTER PROC HumanResources.AddDepartment
    @Name      nvarchar(50),
    @GroupName nvarchar(50),
    @DeptID    smallint OUTPUT
AS
    IF ((@Name = '') OR (@GroupName = ''))
        RETURN -1                        -- error status

    INSERT INTO HumanResources.Department (Name, GroupName)
    VALUES (@Name, @GroupName)

    SET @DeptID = SCOPE_IDENTITY()
    RETURN 0                             -- success
GO

DECLARE @dept int, @result int
EXEC @result = AddDepartment 'Refunds', '', @dept OUTPUT
IF (@result = 0)
    SELECT @dept
ELSE
    SELECT 'Error during insert'

Here @GroupName is empty, so the procedure returns −1 and the caller prints “Error during insert”.

Slide correction

The slide’s caller code ends with SELECT @deptID, but the caller’s variable is @dept (@DeptID exists only inside the procedure). Use SELECT @dept.

Output parameter

Any data type
Can have many
Used for returning data
Needs OUTPUT in CREATE and EXEC

Return value

Integer only
Exactly one
Used for status/error codes
Captured with EXEC @r = proc …

Summary

Definition of stored procedures.
How to create and execute a simple stored procedure.
How to create and execute a parameterized stored procedure (input, output, return).
How to modify (ALTER) and remove (DROP) existing stored procedures.

Quick review

Give five advantages of stored procedures.

Shared application logic, shielding schema details, security (EXECUTE permission), improved performance (single compiled plan), reduced network traffic.

Where must the OUTPUT keyword appear?

In both the parameter declaration in CREATE PROCEDURE and the argument in the EXECUTE statement.

What does a procedure return if RETURN isn’t used?

Write a procedure that lists staff at a given branch (default B003).

CREATE PROC dbo.StaffAtBranch
    @branchNo char(4) = 'B003'
AS
    IF @branchNo IS NULL
    BEGIN
        RAISERROR('Branch required.', 14, 1)
        RETURN -1
    END
    SELECT staffNo, fName, lName, position
    FROM   dbo.Staff
    WHERE  branchNo = @branchNo
    RETURN 0
GO
EXEC dbo.StaffAtBranch @branchNo = 'B005'