Stored Procedure Parameters: Input, Output, Optional

Here you will learn about stored procedure parameters, optional parameters, and executing stored procedures with parameters in SQL Server.

• A stored procedure can have zero or more INPUT and OUTPUT parameters.
• A stored procedure can have a maximum of 2100 parameters specified.
• Each parameter is assigned a name, a data type, and direction like Input, Output, or Return. If a direction is not specified, then by default, it is Input.
• You can specify a default value for the parameters.
• Stored procedures can return a value to the calling program if the parameter is specified as OUTPUT.
• The parameter values must be a constant or a variable. It cannot be a function name.
• Parameter variables can be either user-defined or system variables like @spid

Stored Procedure with Input Parameters

Consider the following stored procedure example with the input parameters.

CREATE PROCEDURE uspUpdateEmpSalary
(
     @empId int
     ,@salary money
)
AS
BEGIN
    UPDATE dbo.Employee
    SET Salary = @salary
    WHERE EmployeeID = @empId
END

In the above stored procedure uspUpdateEmpSalary, the @empId and @Salary are INPUT parameters. By default, all the parameters are INPUT parameters in any stored procedure unless suffix with OUTPUT keyword.@empId is of int type and @salary is of money data type. You pass the INPUT parameters while executing a stored procedure, as shown below.

EXEC dbo.uspUpdateEmpSalary @EmpId = 4, @Salary = 25000
-- or
EXEC dbo.uspUpdateEmpSalary 4, 25000

Parameter Names

• The stored procedure parameters names must start with a single @.
• The name must be unique in the scope of the stored procedure.
• If parameter values are passed as @Param1 = value1, @ Param2 = value2 as shown in the above example, then the parameters can be passed in any order.
• If one parameter is supplied as @param1 = value, then all parameters must be supplied in the same manner.

OUTPUT Parameters

The OUTPUT parameter is used when you want to return some value from the stored procedure. The calling program must also use the OUTPUT keyword while executing the procedure.

The following stored procedure contains INPUT and OUTPUT parameters.

CREATE PROCEDURE uspGetManagerID  
   @empId int,  
   @managerId int OUTPUT  
AS  
BEGIN  
   SELECT @managerId = ManagerID
   FROM dbo.Employee
   WHERE EmployeeID = @empId  
END

In the above uspGetManagerID stored procedure, @manageId is an OUTPUT parameter. The value will be assigned in the stored procedure and returned to the calling statement. The following pass the OUTPUT parameter while executing the stored procedure.

DECLARE @managerID int

EXECUTE uspGetManagerID @empId = 2, @managerId OUTPUT

PRINT @managerId

Above, the uspGetManagerID is called by passing INPUT parameter @employeeID = 2 and @managerID OUTPUT as the output parameter. Notice that we have not assigned any value to an OUTPUT variable @managerID and also specified the OUTPUT keyword.

There are a total of three methods of returning data from a stored procedure: OUTPUT parameter, result sets, and return codes.

Result sets: If the body of the stored procedure has a SELECT statement, then the rows returned by the select statement are directly returned to the client.

Return code: A stored procedure can return an integer value called the Return code which will indicate the execution status of the procedure. You specify the return code using the RETURN keyword in the procedure.

Optional Parameters

SQL Server allows you to specify the default values for parameters. It allows you to skip the parameters that have default values when calling a stored procedure.

The default value is used when no value is passed to the parameter or when the DEFAULT keyword is specified as the value in the procedure call.

Specify the default value when you declare parameters, as shown below.

CREATE PROCEDURE uspUpdateEmpSalary
(
     @empId int
     ,@salary money = 1000
)
AS
BEGIN
    UPDATE dbo.Employee
    SET Salary = @salary
    WHERE EmployeeID = @empId
END

Above, @empsalary money = 0 declares @salary parameter and assigns the default value. Now, you can call the above procedure without passing @salary parameter, as shown below.

EXEC uspUpdateEmpSalary 4

The above statement will update the Salary column with the default value 1000 for the EmployeeID 4. Thus, making @salary parameter as optional.