Database - Stored procedure

Declaration

<AMSQLSTOREDPROC PROCEDURE="text" CONNECTIONTYPE="text (options)" CONNECTIONSTRING="text (encrypted)" PREDEFINEDCONNECTION="text" CONNECTION="text" LOGIN="YES/NO" PROCEDURE="text" NAMES="text" VARIABLES="text" TYPES="text" DIRECTIONS="text" INCLUDEMILLSECONDS="YES/NO" VALUES="text" "PRECISIONS="number" NUMSCALES="number" SIZES="number" ATTRIBUTES="text" TIMEOUT="number" MEASURE="text (options)" RESULTDATASET="text" /></AMDATABASE>  />

Related Topics    

Description

Executes a stored procedure by way of OLEDB on the data source 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.

NOTE: Because the results retrieved from a query may contain multiple records (rows) and multiple fields (columns), to access the data, you must use the Loop Dataset activity to iterate the records and embedded expressions in order to extract the data from the individual fields. See Datasets below for more information.

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.

Parameters

Connection

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:
  • Host (default) - A new or existing connection string will be set individually for this activity. Select this option if performing a single database operation.
  • Session - The connection string should derive from a session created by a previous Database - Open SQL connection step. This allows several queries to be linked to a specific session.
  • System - The connection string will derive from one configured and saved for the system.
Type Text (options) Yes if Connection set to Host Database
  • CONNECTIONTYPE="database"
  • CONNECTIONTYPE="connection_string"
  • CONNECTIONTYPE="file"
 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 (default)- Database connection will derive from a new OLEDB connection string created by way of the Data Link Properties Wizard. Click Build Connection to open the Data Link Properties Wizard. Click Clear to clear an existing connection string.
  • Connection string - Database connection will be established using a specific connection string. Enables you to copy an existing string onto the properties of this activity.
  • Data link file - Connection will be established by way of the data link file specified.
Database connection Text Yes if Type parameter is 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, click Build connection. This opens the Data Link Properties Wizard dialog which will aid in the construction of your connection string. To clear an existing connection string, click Clear. This parameter is available only if the Type parameter is set to Database.
Connection string Text Yes if Type parameter is 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 parameter is set to Data link file (Empty) CONNECTION="c:\temp\fileName.udl" The path and file name 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 for username and password Yes/No No No LOGIN="YES" If selected, specifies that the user should be prompted to enter a username and password for the database server each time the task runs. This parameter is disabled by default.

Stored Procedure

Property Type Required Default Markup Description
Stored Procedure Text Yes (Empty) PROCEDURE="EXECUTE sp_GetInventory 'FL'" Specifies the stored procedure to execute. If the Connection parameter is set to Host or System, click the green arrow  to load existing stored procedures then click the down arrow to select from a drop-down list of stored procedures. If the Connection parameter is set to Session, existing stored procedures are loaded during a previous Databse - Open SQL connection step, bypassing the need to click the green arrow. Instead, simply click the down arrow and select from the drop-down list of stored procedures that appear.
Show system stored procedures --- --- --- --- If enabled, system defined stored procedures will be displayed in the drop-down list (disabled by default). System stored procedures are useful in performing administrative and informational activities in SQL Server. This is a design time parameter mainly used during task construction, therefore, contains no markups.
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. Available parameters are:

  • Name - The unique name of the stored procedure parameter. The parameter name can be used in the stored procedure to obtain and change the value of the parameter.
  • Value - The value of the parameter. You can create a stored procedure with optional parameters by specifying a default value. Note that if the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number (for example, 6xxx), it must be enclosed in single, straight quotation marks.
  • Data type - The parameter's data type. Parameters in a stored procedure are defined with a data type, much as a column in a table is defined. A stored procedure parameter can be defined with any of the SQL Server data types, except the table data type. Stored procedure parameters can also be defined with CLR user-defined types and Transact-SQL alias data types.
  • Direction - The direction of a parameter is either in, meaning a value is passed to the stored procedure input parameter, or out, meaning the stored procedure returns a value to the calling program by way of an output parameter. The default is an input parameter.

Advanced

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:
  • Milliseconds - The timeout value is measured in milliseconds.
  • Seconds (default) - The timeout value is measured in seconds.
  • Minutes - The timeout value is measured in minutes.
  • Hours - The timeout value is measured in hours.
Create and populate dataset 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 in order to recurs the rows of the dataset. More details regarding datasets can be found below under Datasets.
Included milliseconds for dataset DateTime type Yes/No No No INCLUDEMILLISECONDS=YES If selected, milliseconds associated with DateTime dataset values are included in the query results. This parameter is disabled by default.

Description

Error Causes

On Error

Additional notes

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 activity 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

NOTE:
  • Copy and paste the sample AML code below directly into the Task Builder Steps Panel.
  • To successfully run the sample code, update parameters containing user credentials, files, file paths, or other information specific to the task to match your environment.

Description

This sample task executes a stored procedure.

Copy 
AMDATABASE ACTIVITY="stored_procedure" SESSION="DatabaseSession1" PROCEDURE="EXECUTE sp_GetInventory 'FL'"><PARAMETER SIGNED="True" NULL="False" LONGBINARY="False" NAME="name" VALUE="value" DATATYPE="String" DIRECTION="Input" SIZE="2" NUMERIC="22" PRECISION="2" RESULTVARIABLE="var_1" /></AMDATABASE>