CREATE PROCEDURE (Transact-SQL) - SQL Server Documentation

Defines a stored procedure.

Syntax


CREATE PROCEDURE procedure_name
    [  [@parameter data_type [= default ] [ OUTPUT ] ]
    [ ,...] --parameter list
AS
BEGIN
    -- SQL statements that make up the procedure
    SELECT 'Hello, World!' AS Message;
END
GO

Parameters

You can define input and output parameters for stored procedures. Parameters are optional.

Parameter	Description
`procedure_name`	The name of the stored procedure. Must be unique within the schema.
`@parameter`	The name of a parameter. Must start with '@'.
`data_type`	The data type of the parameter.
`default`	The default value for the parameter if no value is provided.
`OUTPUT`	Specifies that the parameter is an output parameter.

Description

CREATE PROCEDURE is used to create a stored procedure in SQL Server. Stored procedures are precompiled SQL statements that can be executed on demand. They offer benefits such as improved performance, modularity, and security.

A stored procedure is a logical unit that can contain one or more T-SQL statements. It can accept input parameters and return results sets or output parameters.

Example

This example creates a simple stored procedure that greets a user.


CREATE PROCEDURE usp_GreetUser
    @UserName VARCHAR(50)
AS
BEGIN
    -- Display a greeting message
    PRINT 'Hello, ' + @UserName + '!';
END
GO

-- To execute the procedure:
EXEC usp_GreetUser 'Alice';

Additional Options

You can specify execution permissions for the stored procedure using the WITH EXECUTE AS clause. This allows you to run the procedure as a different user, which can be useful for managing security and data access.


CREATE PROCEDURE usp_SecureData
    @UserID INT
WITH EXECUTE AS LoginName = 'DataAccessUser'
AS
BEGIN
    -- Access data based on UserID
    SELECT Column1, Column2
    FROM YourTable
    WHERE ID = @UserID;
END
GO

The WITH RECOMPILE option forces SQL Server to recompile the stored procedure every time it is executed. This can be beneficial if the procedure's execution plan might become stale due to frequent data changes.


CREATE PROCEDURE usp_GetLatestOrders
    @OrderCount INT
WITH RECOMPILE
AS
BEGIN
    -- Select the most recent orders
    SELECT TOP (@OrderCount) OrderID, OrderDate
    FROM Orders
    ORDER BY OrderDate DESC;
END
GO

Use the WITH ENCRYPTION option to encrypt the stored procedure's definition. This prevents users from viewing the T-SQL code of the procedure through system catalog views.


CREATE PROCEDURE usp_SensitiveOperation
    @DataValue INT
WITH ENCRYPTION
AS
BEGIN
    -- Perform a sensitive operation that should not be visible
    UPDATE SensitiveData
    SET Value = Value + @DataValue
    WHERE ID = 1;
END
GO