Lesson 4	Creating stored procedures \| User Defined
Objective	Understand how to create a user-defined stored procedure in SQL Server.

Create User-Defined Stored Procedures in SQL Server

A user-defined stored procedure is a named T-SQL program stored in a database. You create a procedure when you want a repeatable, permission-controlled operation (for example, “insert a new customer,” “apply a pricing rule,” or “close an invoice”) that multiple applications can execute consistently.

This lesson shows two common creation workflows and then focuses on the T-SQL syntax you should use in modern SQL Server development.

Preferred Workflow: Scripted T-SQL (Version Controlled)

In production environments, the recommended approach is to create procedures using a scripted T-SQL file that can be reviewed, source-controlled, and deployed consistently across environments (dev → test → prod). This reduces “click-only” changes that are difficult to audit and reproduce.

Use these best practices when writing stored procedures:

Schema-qualify procedure names (for example, dbo.usp_DoWork).
Prefer CREATE OR ALTER for idempotent deployments.
Use parameters for inputs/outputs; validate inputs early.
Use SET NOCOUNT ON; in most procedures to reduce rowcount chatter to client applications.
Keep procedures focused: one clear unit of work per procedure.

Optional Workflow: GUI Tooling

You can also create procedures through a graphical interface in a management tool (for example, SQL Server Management Studio). The specific screens vary by version, but the concept is the same: you supply T-SQL and execute it against the target database.

Creating a new stored procedure using a management tool (UI varies by SQL Server version).

Core Syntax: CREATE OR ALTER PROCEDURE

To create (or update) a user-defined stored procedure using T-SQL, use CREATE OR ALTER PROCEDURE. The following example shows a modern, deployment-friendly template.

The `CREATE OR ALTER` syntax for stored procedures was introduced in SQL Server 2016 Service Pack 1 (November 2016) to simplify database development by eliminating the need for conditional logic that checks whether a procedure exists before creating or modifying it. This feature makes deployment scripts idempotent, allowing them to be executed repeatedly without errors—a significant improvement for teams practicing continuous integration and deployment. SQL Server versions prior to 2016 SP1, including SQL Server 2012, 2014, and the original 2016 RTM release, cannot use this syntax and must rely on workarounds like `DROP IF EXISTS` followed by `CREATE`, or conditional checks against system catalog views. Organizations running older SQL Server versions should consider upgrading to take advantage of this and other modern T-SQL enhancements that align database development with contemporary DevOps practices.

Creating a stored procedure using CREATE OR ALTER PROCEDURE syntax — `CREATE OR ALTER PROCEDURE dbo.procedure_name @parameter1 data_type = default, @parameter2 data_type OUTPUT AS BEGIN -- Transact-SQL statements END;`

Syntax Walkthrough

The ordered list below explains each part of the template shown under the figure.

CREATE OR ALTER PROCEDURE creates the procedure if it does not exist, or alters it if it already exists. This supports repeatable deployments.
dbo is the schema. Schema-qualify procedure names so SQL Server resolves the object consistently and predictably.
procedure_name is the procedure identifier. Use a clear naming convention (many teams use a prefix like usp_ for user stored procedures).
@parameter1 is an input parameter name. Parameters are the primary mechanism for passing values into a stored procedure.
data_type defines the parameter datatype (for example, int, nvarchar(50), datetime2). Every parameter must declare a datatype.
= default assigns a default value to the parameter, used when the caller does not supply an explicit value.
The comma between parameters allows multiple parameters to be declared in a single procedure signature.
OUTPUT marks a parameter as output-capable so the procedure can return a value through that parameter to the caller.
AS begins the procedure body definition (the executable T-SQL section).
BEGIN … END forms the procedure body block that contains one or more T-SQL statements implementing the unit of work.
-- Transact-SQL statements is where you implement the logic (queries, inserts/updates, validation, transactions, and error handling as required).

Practical Example

The following example demonstrates two best practices: SET NOCOUNT ON and a simple parameterized query.

CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeById
    @EmployeeId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT e.EmployeeId, e.FirstName, e.LastName, e.DepartmentId
    FROM dbo.Employees AS e
    WHERE e.EmployeeId = @EmployeeId;
END;

Execute the procedure like this:

EXEC dbo.usp_GetEmployeeById @EmployeeId = 42;

In the next lesson, you will practice creating a user-defined stored procedure and refining it with parameters and business rule logic.