Automation Action: Execute A Database Command

Executes a SQL Command or Stored Procedure with optional parameters and returns multiple return values.

This Action allows you to execute a SQL Statement or Stored Procedure. You can pass any number of parameters and assign output parameters to variables.

Select a Database Type to connect to from the list. You must specify a Connection String that ThinkAutomation will use to open the database. Click the ... button to build the connection string. Click the Test button to verify that ThinkAutomation can connect to the database.

Select the Command Type. This is either a SQL Statement or Stored Procedure.

Specify the Command SQL Statement or Stored Procedure Call depending on the command type. You can substitute parameters using @parametername in the SQL Command statement.

Command Parameters

You can pass multiple parameters to your command.

For each parameter in the SQL statement you must specify the Name, Type, Direction & Size. These must match your stored procedure parameters types when using a store procedure, or column types when using a SQL statement. See: SQL Parameters.

For Output Parameters and the Return Value you can specify the variable to Assign Result To.

For Input Parameters you set the Value - this can be fixed or a %variable% replacement.

Blob Data (Saving File Contents)

For parameters with type Blob - if the Value assigned is a file path, then the file contents are read and the binary data is assigned to the Value.

Saving Attachments

If you want to store message attachments to a database you can use a For..Each action to loop on Attachment. Inside the loop set variables for the Filename and Temporary Location values. You can then assign these variables to the relevant database parameter values. See: Example.

Execute Method

Select Non Query if your SQL statement does not return a result set. You can optionally assign the rows affected to a variable selected from the Assign Rows Affected To list.

Select Scalar if your SQL statement returns a result set. The first column of the first row of any results can be optionally be assigned to a variable selected from the Assign Result To list. For example, the SQL statement:

INSERT INTO "Production"."ProductCategory" (Name) VALUES (@Name);
SELECT scope_identity();              

The above SQL Server statement will insert a new record and then return the new identity value. The returned value can be assigned to a variable.