Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

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.

Example: Stored Procedure with 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.

Example: Passing INPUT Parameters
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.

Example: Stored Procedure with OUTPUT Parameter
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.

Example: Passing OUTPUT Parameter
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.

Example: Stored Procedure with Optional Parameter
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.

Example: Calling Stored Procedure
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.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.