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.
- Different Types of Parameters in SQL Server Stored Procedure.
- Understanding the Input Parameters in Stored Procedure.
- 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
- Input parameters
- 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.