Input Parameters in SQL Server Stored Procedure

Back to: SQL Server Tutorial For Beginners and Professionals

Input Parameters in SQL Server Stored Procedure

In this article, I am going to discuss the Input Parameters in SQL Server Stored Procedure with examples. Please read our previous article before proceeding to this article where we discussed the basics of SQL Server Stored Procedure with some examples. As part of this article, we are going to discuss the following pointers.

1. Different Types of Parameters in SQL Server Stored Procedure.
2. Understanding the Input Parameters in Stored Procedure.
3. Examples of SQL Server Procedure with Input Parameters.

Different Types of Parameters in SQL Server Stored Procedure.

The parameters of a Stored Procedure in SQL Server can be of two types

1. Input parameters
2. Output parameters

The Input Parameters in SQL Server Stored Procedure are used for bringing the values into the procedure for execution. On the other hand, the Output Parameters are used to carrying a value out of the procedure after execution.

When a parameter is declared with the output keyword, then we only require assigning a value to the parameter inside the procedure so that the procedure will send that value out at the end of procedure execution.

Understanding the Input Parameters in SQL Server Procedure:

Let us understand the Input Parameters in SQL Server with an example. Let’s create a procedure which will take two input integer parameters and then perform the sum operation and finally print the result.

Example1: Stored Procedure for adding two variables value

-- Create a Procedure
ALTER PROCEDURE spAddTwoNumbers(@no1 INT, @no2 INT)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = @no1 + @no2
  PRINT 'RESULT IS: '+ CAST(@Result AS VARCHAR)
END
GO

-- Calling the procedure:
EXECUTE spAddTwoNumbers 10, 20

-- OR 
EXECUTE spAddTwoNumbers @no1=10, @no2=20

-- OR calling the procedure by declaring two variables as shown below
DECLARE @no1 INT, @no2 INt
SET @no1 = 10
SET @no2 = 20
EXECUTE spAddTwoNumbers @no1, @no2

Note: The Parameters and variables that we created must have an @ prefix in their name.

Let’s see some more examples for a better understanding of the SQL Server stored procedure Input Parameters.

We are going to work with the following Employee table.

Please use the following SQL Script to create and populate the Employee table.

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO

Example2: Create Procedure to get the employee information bypassing the employee gender and department id from the Employee table

CREATE PROCEDURE spGetEmployeesByGenderAndDepartment
  @Gender VARCHAR(20),
  @DeptID INT
AS
BEGIN
  SELECT Name, Gender, DOB, DeptID 
  FROM Employee
  WHERE Gender = @Gender AND DeptID = @DeptID
END
GO

In order to invoke the above SQL Server Stored Procedure, we need to pass the value for @Gender and @DeptID input parameters. If we don’t specify the name of the parameters we have to first pass the value for the @Gender parameter and then for the @DeptID parameter as shown below.

EXECUTE spGetEmployeesByGenderAndDepartment ‘Male’, 1

On the other hand, if we change the order, then we will get an error stating “Error converting data type varchar to int.” This is because the value of “Male” is passed into the @DeptID parameter. Since @DeptID is an integer, we get the type conversion error.

EXEC spGetEmployeesByGenderAndDepartment 1, ‘Male’

When we specify the names of the parameters when executing the stored procedure the order doesn’t matter. The example is given below.

EXEC spGetEmployeesByGenderAndDepartment @DeptID=1, @Gender=’Male’

NOTE: While we are calling the stored procedure passing the values in the order is very important. In the order they are declared in the procedure we need to pass the values in the same order. You can also pass the value in any order, but at that time you have to specify the variable name before the value to which you are passing the values.

Example3: Create a procedure to update the Employee details in the Employee table based on the Employee id.

-- Create a Procedure
CREATE PROCEDURE spUpdateEmployeeByID
(
  @ID INT, 
  @Name VARCHAR(50), 
  @Gender VARCHAR(50), @DOB DATETIME, 
  @DeptID INT
)
AS
BEGIN
  UPDATE	Employee SET 
      Name = @Name, 
      Gender = @Gender,
      DOB = @DOB, 
      DeptID = @DeptID
  WHERE	ID = @ID
END
GO

-- Executing the Procedure
-- If you are not specifying the Parameter Names then the order is important
EXECUTE spUpdateEmployeeByID 3, 'Palak', 'Female', '1994-06-17 10:53:27.060', 3

-- If you are specifying the Parameter Names then order is not mandatory
EXECUTE spUpdateEmployeeByID @ID =3, @Gender = 'Female', @DOB = '1994-06-17 10:53:27.060', @DeptID = 3, @Name = 'Palak'

In the next article, I am going to discuss the SQL Server Stored Procedure Output Parameters with some examples. Here, In this article, I try to explain the Input Parameters in SQL Server Stored Procedure step by step with some examples.