Stored Procedure Action Icon

Stored Procedure

 

Declaration

<AMSQLSTOREDPROC PROCEDURE="text" NAMES="text" VARIABLES="text" TYPES="text" DIRECTIONS="text" VALUES="text" PRECISIONS="number" NUMSCALES="number" SIZES="number" ATTRIBUTES="text" />

See Also

Open SQL Connection | Close SQL Connection | SQL Query | Web Service | FTP (File Transfer Protocol) | HTTP (Hypertext Transfer Protocol) | MSMQ (Microsoft Message Queuing) | Microsoft SharePoint

Description

Executes the selected stored procedure via OLEDB on the datasource specified. Stored procedures are configured on the database server and are often faster than repeated SQL calls. Some key benefits include:

Practical Usage

Useful for automated retrieval, update and management of data. Could also be used to test database response times and size quotas. A working knowledge of SQL will help most people get the most out of this action.

NOTE: Because the results retrieved may contain multiple records (rows) and multiple fields (columns) - to access data retrieved from SQL Query you must use the Loop Dataset action to iterate the records and embedded expressions in order to extract the data from the individual fields (i.e. %datasetname.fieldname%). See notes below for more information.

Parameters

General Properties

Property

Type

Required

Default

Markup

Description

Connection String Type

 

 

 

 

Indicates where this action's connection string should originate from. The connection string includes the source database name, driver, username, password and other properties needed to establish the initial connection. Different parameters are available depending on the option selected. The available options are:

  • Use custom connection string (Default) - Specifies that a custom OLEDB connection string will be set for this action. Select this option if performing a single query.

  • Use existing SQL connection from a session - Specifies that a connection string should derive from a session created by a previous Open SQL Connection step. This allows several queries to be linked to a specific session.

  • Use pre-defined connection string - Specifies that the connection should derive from a variable or constant.

Connection String

Text

Yes if custom connection string is selected

(Empty)

CONNECTIONSTRING=

"Encrypted_data"

Specifies an OLEDB connection string. Click the Data Link Properties button to open the Data Link Properties Wizard dialog allowing you to setup a custom connection string. Valid only if Use custom connection string is selected in the Connection String Type parameter. For more details on setting, see Data_Link_Properties_Wizard.

Session Name

Text

Yes if connection string originates  from a session

SQLSession1

SESSION="Session10"

Indicates the session name that this connection should derive from. Valid only if Use existing SQL connection from a session is selected in the Connection String Type parameter.

Pre-defined Connection String

Text

Yes if pre-defined connection string is selected

(Empty)

PREDEFINEDCONNECTION=

"connection"

Indicates the pre-defined connection string that should be used. Valid only if Use pre-defined connection string is selected in the Connection String Type parameter.

Prompt user for a name and password

Yes/No

No

No

LOGIN="YES"

Specifies that the user should be prompted to enter a username and password for the database server each time the task is run. Valid only if Use custom connection string or Use pre-defined connection string is selected in the Connection String Type parameter.

Stored Procedure

Text

Yes

(Empty)

PROCEDURE="EXECUTE sp_GetInventory 'FL'"

Specifies the stored procedure to execute.

Parameters

Text

No

(Empty)

NAMES="Name"

VARIABLES="var_1"

TYPES="String" DIRECTIONS="Input" VALUES="Value"

PRECISIONS="2"

NUMSCALES="22"

SIZES="2"

ATTRIBUTES="Signed"

Specifies the parameters of the stored procedure to execute. Click the Add button to add parameters.

Advanced Properties

Property

Type

Required

Default

Markup

Description

The query times out after (optional)

Number

No

(Empty)

TIMEOUT="2"

MEASURE="minutes"

Specifies the amount of time that the query should be allowed before timing out at the server level. Simply enter a value and select from the following time intervals:

  • milliseconds

  • seconds (default)

  • minutes

  • hours

Create and populate dataset (optional)

Text

No

(Empty)

RESULTDATASET="theData"

Indicates the name of the dataset that should be created and populated with the results (if any) of the stored procedure upon execution. To access the data in subsequent steps simply specify %datasetname.fieldname% (where datasetname specifies the name of the dataset and fieldname specifies the name of the dataset field) inside a Loop Dataset action (to recurse the rows).

More details regarding datasets can be found below under Additional Notes.

↑ [Top of Page]

 

Description Properties

The Description tab allows you to customize the text description of any step as it appears in the Task Builder's Steps Pane.

More on setting custom step description

Error Causes Properties

The Error Causes tab allows you to select/omit specific errors that should cause a particular step to fail.

More on Error Causes properties

On Error Properties

The On Error tab allows you to determine what the task should do if a particular step encounters an error.

More about On Error properties

Additional Notes

Datasets

A dataset is a multiple column, multiple row container object. This action creates and populates a dataset, the fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:

 

 

SELECT firstname, lastname, company from customer where city='Los Angeles';

 

 

Then the following dataset would be generated (where datasetname specifies the name of the dataset):

 

datasetname.firstname

datasetname.lastname

datasetname.company

 

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

 

%datasetname.firstname%

 

 

or you could combine two fields together like this:

 

%datasetname.firstname + " " + datasetname.lastname%

 

 

Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:

 

 <AMMESSAGEBOX MESSAGETEXT="%datasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

 

 

At runtime the text %datasetname.firstname% is replaced by the contents of the subject of the current record.

More on datasets

Along with the above fields, there are standard fields included in every dataset. The table below describes these fields (assuming the name of the dataset is theDataset):

 

Name

Data Type

Return Value

theDataset.CurrentRow

Number

The current row that will be accessed in the dataset by an expression that does not contain a specific row index.

theDataset.TotalRows

Number

The total number of rows in the dataset

theDataset.TotalColumns

Number

The total number of columns (not including the static columns) in the dataset.

theDataset.ExecutionDate

Date

The date and time the dataset was created and populated

theDataset.RowsAffected

Number

The number of rows affected by an update.

theDataset.SQLQuery

Text

The SQL Query that was used to generate this dataset (If a SQL Query was not used, this value is empty).

theDataset.Datasource

Text

The datasource used for the SQL Query, if applicable.

theDataset.ColumnNames

Text

A comma-delimited list of the column names in the dataset

 

Variables and Expressions

All text fields allow the use of expressions, which can be entered by surrounding the expression in percentage signs (Example: %myVariable% or %Left('Text',2)%). To help construct these expressions, you can open Expression Builder from these fields by clicking the Insert expression/variable button or pressing F2.

More on variables
More on expressions
More on the Expression Builder

Example

NOTE: The code below can be copied and pasted directly into the Steps pane of the Task Builder.

 

Description: Execute the stored procedure: "EXECUTE sp_GetInventory 'FL'".  The parameters are: "Name" (names), "var_1" (variables to hold the values), "String" (types), "Input" (directions), "Value" (values), "2" (max. number of characters that can appear to the right of the decimal place), "22" (max. number of digits allowed in the parameter), "2" (sizes), "Signed" (type of values accepted by the parameter).  

 

<AMSQLSTOREDPROC PROCEDURE="EXECUTE sp_GetInventory 'FL'" NAMES="Name" VARIABLES="var_1" TYPES="String" DIRECTIONS="Input" VALUES="Value" PRECISIONS="2" NUMSCALES="22" SIZES="2" ATTRIBUTES="Signed" />

 

 

↑ [Top of Page]