In this post, we will see how we can use the Stored Procedure in Execute sql server. I have created
an SP in it is use insert a record into the table. I am creating a simple SP
to insert a record into the table.
|
Create Procedure P_Insert_Emp
AS
Begin
insert into emp (E_name,E_mobile,E_add)
values ('BageshKumar','888880XXXX','Greater Noida')
End;
|
Now I am taking execute SQL task and creating the connection
manager.
Since I am inserting a record using SP so that I have
selected ResultSet as null. SQL source type will be the Direct and in SQL
statement I am calling SP. Click Ok.
The package is ready to run
Before running this package records in the table.
Now I am running this package.
Package Executed successfully. See the records in the table.
The above example is called SP that which does not have any
parameter.
Call parameterized SP
Above example, we see how we call non parametrized SP.
Let’s see how we can call parametrized Sp.
I am altering the above SP.
|
alter Procedure P_Insert_Emp
(@E_name varchar(50),
@E_mobile varchar(10),
@E_add varchar(10)
)
AS
Begin
insert into emp (E_name,E_mobile,E_add)
values (@E_name,@E_mobile,@E_add)
End
|
I am doing changes in the existing execute SQL task.
SP expects 3 input parameters. We need to create 3 variables
where we pass this parameter.
In execute sql task we need to pass these parameters with the
?
Here in this SP, I have 3 input parameters.
Go to the Parameter Mapping table and assign the variable in
the sequence.
Note: Parameter Name it must be
in the sequence order of the? Value for example
Parameter index is starting from the 0
Now click ok.
Now the package is ready to run.
Before running this package record in the table
The package executed successfully. See the records in the table
