ExecuteQuery

Estimated reading: 5 minutes 4828 views

This activity helps the user to perform SQL queries against a database. It allows users to execute SELECT statements to retrieve data from a database table or perform other SQL operations that return results, such as counting records, calculating values, or filtering data.

Properties

INPUT

CommandTimeout: This parameter indicates the timeout value for executing the activity within the connected database. If the execution cannot be established within this specified time, it will throw an exception.

It accepts values in “Integer” datatype. You can either enter the values hardcoded in “INT32” format or pass the values as “Int32” datatype.

DBConnection*: This parameter indicates the existing SqlConnection to be provided for execution of the activity. Here, you should mention the output variable declared in the “ConnectDB” activity.

This parameter only accepts values in the “SqlConnection” data type.

Parameters: Indicates to provide the collection of parameters that are required to be passed in the SQL query provided.

This parameter accepts the “Dictionary” datatype, which is bound to the SQL query. Refer the below documentation on how to provide the parameters.

SqlQuery*: This parameter indicates to provide the SQL Command/ query that needs to be executed. You should mention the “SQLQuery” based on the “Command Type” chosen in the “Type” parameter.

It accepts values in the “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as a “String” datatype.

Type: Indicates to choose the “Command Type” that is required to execute along with the provided “SqlQuery”. Select the option from drop-down:

Text: Use this option when you want to execute a straightforward SQL command directly without involving stored procedures.
StoredProcedure: Use this option when you want to execute a pre-defined stored procedure that resides in your database. Refer the below documentation.

MISC

DisplayName: Displays the name of the activity. The activity name can be customized, which aids in troubleshooting.

SkipOnError: Specify the “Boolean” value as “True” or “False.”

True: Continue executing the workflow regardless of any errors thrown.
False: Halt the workflow if it encounters any errors.
None: If the option is left blank, the activity will, by default, behave as if “False” were chosen.

Version: It indicates the version of the feature being used.

OUTPUT

Datatable: It helps to view the output of the activity in a table view format as the output executed from the provided SQL query. It returns the value in “Datatable” datatype.

* Represents mandatory fields to execute the workflow.

Text & Stored Procedure

The choice between using the “Text” and “Stored Procedure” types in the Execute Non-Query activity depends on the nature of the SQL command you want to execute:

1. Text (SQL Query):
a. Use the “Text” type when you want to execute a straightforward SQL command directly in the activity without involving stored procedures.
b. This is suitable for commands like INSERT, DELETE, UPDATE, and other data manipulation tasks where you provide the SQL query directly in the “SQLQuery” parameter.

2. Stored Procedure:
a. Use the “Stored Procedure” type when you want to execute a pre-defined stored procedure that resides in your database.
b. This is beneficial for complex operations or when you have stored procedures designed to handle specific tasks within your database.
c. Instead of providing the SQL query directly, you specify the name of the stored procedure in the “SQLQuery” parameter.

Execute Non-query & Execute Query

Here’s an example of how the activity is used in the workflow –

In the following example, I am continuing from “BulkInsert” activity example, using a query I am going to alter the table and add a new column in addition to it.

The table in the database already contains Name, Position, Project, Location, Salary. In addition to it, I am adding a column as “Email” to it.

Steps to build the bot:

1. Continuing the steps from Bulk Insert activity.
2. Now, I am adding the “ExecuteQuery” activity and placing it below the “Writelog” activity.
a. Here I am using this activity to alter the table using the SQL Query.
b. Navigating to the “DB Connection” property in the properties section to provide the sql connection variable declared as output in the “ConnectDB” activity.
c. Here it is as “DB_OUTPUT”.
d. Next, moving to the “SqlQuery” property to provide the query that satisfies the condition.
e. Here I am providing the query as
“ALTER TABLE Employees
ADD Email VARCHAR(255)”
f. Now, choosing the “Type” as “Text” since here I am directly providing the query to alter the table and not through the stored procedure.
g. Then, moving to the output section of the properties to declare a variable in the “Datatable” field to view the output.
3. To view the output, I am adding a table viewer activity.
a. Here I am providing the “Input Datatable” as “Altered_Table”.
4. Save and execute the workflow.

The bot will create a new column as “Email” in the provided table “Employees”.

ExecuteQuery

Properties

Text & Stored Procedure

Execute Non-query & Execute Query

ExecuteQuery

CONTENTS