Docs Home

Parameters (Transact‑SQL)

Syntax

CREATE PROCEDURE [ schema_name . ] procedure_name 
    [ { @parameter_name [type_schema_name.] data_type 
        [ = default ] [ OUT | OUTPUT ] 
        [ READONLY ] } 
      [ ,...n ] ]
[ WITH  [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Parameter Types

DirectionKeywordDescription
Input(default)Value passed to the procedure.
OutputOUTPUT or OUTReturns a value to the caller.
Read‑Only TableREADONLYAccepts a table‑valued parameter.

Default Values

You can assign a default value to a parameter. If the caller omits the argument, the default is used.

CREATE PROCEDURE dbo.GetOrders
    @CustomerID nchar(5) = NULL,
    @OrderDate datetime = NULL
AS
BEGIN
    SELECT * FROM Orders
    WHERE (@CustomerID IS NULL OR CustomerID = @CustomerID)
      AND (@OrderDate IS NULL OR OrderDate = @OrderDate);
END;

Examples

1. Input and Output Parameters

CREATE PROCEDURE dbo.GetEmployeeCount
    @DepartmentID int,
    @EmployeeCount int OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END;

DECLARE @cnt int;
EXEC dbo.GetEmployeeCount @DepartmentID = 5, @EmployeeCount = @cnt OUTPUT;
SELECT @cnt AS EmployeeCount;

2. Table‑Valued Parameter (READONLY)

CREATE TYPE dbo.OrderItemTableType AS TABLE
(
    ProductID int,
    Quantity int
);
GO

CREATE PROCEDURE dbo.InsertOrder
    @OrderDate datetime,
    @Items dbo.OrderItemTableType READONLY
AS
BEGIN
    INSERT INTO Orders (OrderDate) VALUES (@OrderDate);
    DECLARE @OrderID int = SCOPE_IDENTITY();

    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    SELECT @OrderID, ProductID, Quantity FROM @Items;
END;
GO

DECLARE @tbl dbo.OrderItemTableType;
INSERT INTO @tbl VALUES (101,2),(102,5);
EXEC dbo.InsertOrder @OrderDate = GETDATE(), @Items = @tbl;

Remarks