Execute SQL Script to Read Data

⌘K
  2. Home
  4. Docs
  6. Virto Workflow Automation...
  8. VIRTO SHAREPOINT WORKFLOW...
  10. Database Activities
  12. Execute SQL Script to Read Data

Execute SQL Script to Read Data

This activity executes SQL statements within SharePoint Designer workflows and returns the result back to the workflow.

execute_sql_script_to_read_data_1

Note: The default provider is MS SQL Server (System.Data.SqlClient). You can use other providers like ODBC, Oracle, etc.

This activity supports multiple batches separated by the GO command. GO is not a Transact-SQL statement; it is a command recognized by this activity. The activity interprets GO as a signal to send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements includes all statements entered since the last GO, or since the start of the session if it is the first GO. A Transact-SQL statement cannot occupy the same line as a GO command, though the line can contain comments.

To return a result from the SQL script to the workflow, you should assign the output to the output variable.

Working with Parameter Placeholders

The syntax for parameter placeholders depends on the data provider. The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently, with syntax customized for each data provider. The syntax for common data providers is described in the table below:

Data providerParameter naming syntax
System.Data.SqlClientUses output parameters in the format @output_result.
System.Data.OleDbUses positional parameter markers indicated by a question mark ?.
System.Data.OdbcUses positional parameter markers indicated by a question mark ?.
System.Data.OracleClientUses output parameters in the format :output_result (or output_result).
Example: Sql script for System.Data.SqlClient provider
GO
DECLARE @var1 VARCHAR(10);
SET @var1 = 'sample';
SET @output_result = @var1;
GO
Example: Sql script for System.Data.OleDb provider
GO
DECLARE @var1 VARCHAR(10);
SET @var1 = 'test';
SET ? = @var1;
GO
SharePoint Workflow Designer Phrase

Execute SQL Script this SQL script using this provider name, this connection string, and store result in Variable

Parameters

ParameterDescription
this SQL scriptSQL statements. Supports multiple batches separated by the GO command.
this provider nameThe .NET ADO Provider. Default is MS SQL Server (System.Data.SqlClient). You can also use:
– System.Data.Odbc – ODBC Data Provider
– System.Data.OleDb – OleDb Data Provider
– System.Data.OracleClient – OracleClient Data Provider
– System.Data.SqlServerCe.3.5 – Microsoft SQL Server Compact Data Provider
For more information about providers, see here.
this connection stringThe connection string, e.g., Data Source=localhost\SQLExpress;Initial Catalog=test;Integrated Security=SSPI.
VariableGets or sets the output result.

How can we help?