Executing a Stored Procedure in UiPath

How to connect to SQL SERVER & execute a parametrized Stored procedure in UiPath Studio ?

Please find the below sequence of steps for a stored procedure execution. 

1. SQL SERVER:

  • Create a SQL table "tblUserDetails" with columns as Username, RoleName etc
  • A parametrized stored procedure which pulls the information based on the provided Username
  • Enter few rows of sample data

Below snapshot details out the Table & the Stored Procedure created on the server side: 

   ​  ​ 
2. Workflow Steps: (Using UiPath Studio) 

  • Add a Sequence Activity
  • Include 3 different activities to pull info from Sql Server
    • Connect - Establish a connection to the Sql Server
    • Execute Query - Execute stored procedure
    • Disconnect - Close the active connection

3. Connect Activity 

  • Provide the ConnectionString & ProviderName properties
  • ConnectionString includes the Server , Database , User Id , password details
  • Provider Name is System.Data.SqlClient if you are connecting to Sql Server

  • Provide any meaningful name for the Output Database Connection.(Ex: DBString)

 

 ​ 


4. Execute Query Activity 
Provide the Necessary fields :

  • ExistingDbConnection = DBString.
  • Sql = Store procedure query name.
  • CommandType = StoreProcedure .
  • DataTable = any meaningful name (outEmployee)
  • We need to pass the arguments(collection of arguments) in Parameters .

 

 ​ 

 ​

5. Disconnect Activity: 

  • Disconnect the active connection to the server. 

 ​ 

 

  • On execution the outline window displays all the retrieved rows details:

 ​ 

  • The workflow with all the 3 Activity will look similar to the below screenshot: