CREATE PROCEDURE (Transact-SQL)

Creates a stored procedure in SQL Server.

Syntax


CREATE { PROC | PROCEDURE } [ { database_name.schema_name. | schema_name. } ] procedure_name
    [ { @parameter [ OUT | OUTPUT ] } 
        [ = default ] [ OUTPUT ]
    ]
    [ ,...n ]
[ WITH  ]
[ FOR REPLICATION ]
AS
    [  ]
    [ ... ]

Arguments

Parameter	Description
`database_name.schema_name. \| schema_name.`	Specifies the database and schema in which to create the procedure. If omitted, the current database is used.
`procedure_name`	The name of the stored procedure. Must be unique within the schema.
`@parameter`	A parameter name, which must start with an @ symbol.
`data_type`	The data type of the parameter.
`= default`	The default value for the parameter.
`OUTPUT`	Indicates that the parameter is an output parameter.
`WITH <options>`	Specifies options for the stored procedure. Examples include `RECOMPILE`, `ENCRYPTION`, `EXECUTE AS`.
`FOR REPLICATION`	Indicates that the procedure is used only for replication.
`AS`	Required keyword preceding the procedure body.
`<sql_statement>`	The Transact-SQL statements that make up the body of the stored procedure.

Note: The procedure name can be a maximum of 128 characters, unless it is a temporary procedure.

Remarks

Stored procedures are precompiled sets of one or more Transact-SQL statements that are stored on the server. They can be executed as a single unit, which reduces network traffic and improves performance. Stored procedures allow for modularity, reusability, and enhanced security.

Permissions

Requires CREATE PROCEDURE permission in the current database, or ALTER ANY PROCEDURE permission.

Important: When creating a stored procedure, ensure you define appropriate parameters, handle potential errors using TRY...CATCH blocks, and consider security implications such as EXECUTE AS clauses.

Example 1: Simple Stored Procedure


-- Create a simple stored procedure to get employee names
CREATE PROCEDURE dbo.GetEmployeeNames
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees;
END;
GO

This example creates a procedure named GetEmployeeNames in the dbo schema that selects all employee IDs, first names, and last names from the Employees table.

Example 2: Stored Procedure with Parameters


-- Create a procedure to get customers by city
CREATE PROCEDURE dbo.GetCustomersByCity
    @CityName NVARCHAR(50)
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers
    WHERE City = @CityName;
END;
GO

-- Execute the procedure
EXEC dbo.GetCustomersByCity @CityName = 'London';

This example creates a procedure that accepts a @CityName parameter and returns customers from that city.

Example 3: Stored Procedure with OUTPUT Parameter


-- Create a procedure to get the count of orders for a customer
CREATE PROCEDURE dbo.GetOrderCount
    @CustomerID INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(*)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
GO

-- Declare a variable to hold the output
DECLARE @count INT;

-- Execute the procedure and retrieve the output
EXEC dbo.GetOrderCount @CustomerID = 1, @OrderCount = @count OUTPUT;

-- Print the result
PRINT 'Number of orders: ' + CAST(@count AS VARCHAR(10));

This example demonstrates how to use an output parameter to return a value from the stored procedure.