Database - Stored Procedure
Declaration
<AMSQLSTOREDPROC PROCEDUE="text" NAMES="text" VARIABLES="text" TYPES="text" DIRECTIONS="text" VALUES="text" PRECISIONS="number" NUMSCALES="number" SIZES="number" ATTRIBUTES="text" />
Description: Executes a stored procedure via OLEDB on the datasource specified. Stored procedures are configured on the database server and are often faster than repeated SQL calls. A working knowledge of SQL is necessary to get the most out of this activity.
Practical Usage
Useful for automated retrieval, update and management of data. Could also be used to test database response times and size quotas. Some key benefits include:
-
Precompiled execution - SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
-
Reduced client/server traffic - If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
-
Efficient reuse of code and programming abstraction - Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
-
Enhanced security controls - You can grant users permission to execute a stored procedure independently of underlying table permissions.
General Parameters
Property |
Type |
Required |
Default |
Markup |
Description |
---|---|---|---|---|---|
Connection |
|
|
|
|
Indicates where the connection string should originate from. This is a design time parameter used only during task construction and configuration, thus, comprises no markup. The available options are:
|
Type |
Text (options) |
Yes if Connection set to Host |
|
|
The type of connection string to use. This parameter is active only of the Connection parameter is set to Host. The available options are:
|
Database connection |
Text |
Yes if Type set to Database |
(Empty) |
CONNECTIONSTRING= "AM1P2qCkbYiFHdqWN7qDphOEw==aME" |
Specifies a custom OLEDB connection string. The connection string provides driver, server, username, password and other important information to the OLEDB subsystem so that the connection can be made. To generate a connection string, press the Build connection button. This opens the Data Link Properties Wizard dialog which will help you with construct of your connection string. To clear an existing connection string, press the Clear button. This parameter is available only if the Type parameter is set to Database. |
Connection string |
Text |
Yes if Type set to Connection string |
(Empty) |
PREDEFINEDCONNECTION= "%MyPreDefinedConnection%" |
Specifies the OLEDB connection string. Use this option to copy an existing connection string and paste it in the provided text box. This parameter is available only if the Type parameter is set to Connection string. |
Data link file |
Text |
Yes if Type set to Data link file |
(Empty) |
CONNECTION="c:\temp\fileName.udl" |
The path and filename of the data link (.udl,.dsn) file to connect with. Click the folder icon to open a standard Explorer dialog allowing for easy navigation to the desired data link file. This parameter is active only if the Type parameter is set to Data link file. |
Prompt user for a name and password |
Yes/No |
No |
No |
LOGIN="YES" |
If set to YES, specifies that the user should be prompted to enter a username and password for the database server each time the task is run.Set to NO by default. |
Protect connection string |
|
|
|
|
If enabled, the value entered in the Connection string parameter will be encrypted in both visual and AML mode as a means of protecting data embedded in the connection string. If disabled (default), the connection string will appear as normal readable text. Enable this option if your connection string contains sensitive data that you may not want others to view. This is a visual mode parameter used only during design time, therefore, contains no properties or markup. It is available only if the Type parameter is set to Connection string. |
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. |
Stored Procedure Parameters
Property |
Type |
Required |
Default |
Markup |
Description |
---|---|---|---|---|---|
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. This opens a Stored Procedure Parameters dialog in which to enter the desired parameters. Upon completion, a new line of parameters will appear in Parameters grid. To edit an existing line of parameters, highlight it and click the Edit button. To delete an existing line of parameters, highlight it and click the Delete button. |
Advanced Parameters
Property |
Type |
Required |
Default |
Markup |
Description |
---|---|---|---|---|---|
The stored procedure times out after (optional) |
Number |
No |
(Empty) |
TIMEOUT="2" |
The amount of time that the query should be allowed before timing out at the server level. |
Measure |
Text (options) |
No |
Seconds |
MEASURE="minutes" |
The time measurement that should correspond to the stored procedure timeout value entered. The available options are:
|
Create and populate dataset (optional) |
Text |
No |
(Empty) |
RESULTDATASET="theData" |
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 activity (to recurse the rows). More details regarding datasets can be found below under Datasets. |
Description tab - A custom description can be provided on the Description tab to convey additional information or share special notes about a task step.
Error Causes tab - Specify how this step should behave upon the occurrence of an error. (Refer to Task Builder > Error Causes Tab for details.)
On Error tab - Specify what AWE should do if this step encounters an error as defined on the Error Causes tab. (Refer to Task Builder > On Error Tab for details.)
Datasets
A dataset is a multiple column, multiple row container object. This activity 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.
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 |
Example
The sample AML code below can be copied and pasted directly into the Steps panel 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" />