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: