For
executing the SPs we are using Execute SQL task.
Let’s see
how we can use an output parameter in SP.
I am
creating a simple SP with an output parameter.
|
Create Procedure P_Insert_Emp
(@E_name varchar(50),
@E_mobile varchar(10),
@E_add varchar(10),
@Number_of_Records
int output
)
AS
Begin
insert into emp (E_name,E_mobile,E_add)
values (@E_name,@E_mobile,@E_add)
Select @Number_of_Records=Count(*) from emp
End;
|
In this SP I am inserting a record in each run and getting
the count of all records in the output parameter.
I am taking execute SQL task and creating the connection
manager. For the input and output parameter, we need to create variables to store
the value.
Before that, I am creating the variable to store the values
Now we go the parameter mapping.
Keep in mind for the output
parameter we need to select Direction as Output.
Click ok.
To see the value of the output parameter I am taking
script tasks.
In C# script I am writing below code to see the message.
Now the package is ready to execute
Before running this package records in the table.
3 records.
Now I am running this package
See the records in the table.
