Iron Speed Designer Help - Transact-SQL Stored Procedures for Microsoft SQL Server

Transact-SQL Stored Procedures for Microsoft SQL Server

Iron Speed Designer creates the following stored procedures for each database table and database view used in your application:

Transact-SQL Stored Procedures	Purpose
<PREFIX>Add	Insert a single record in the table.
<PREFIX>Delete	Delete a single record from the table.
<PREFIX>DeleteRecords	Delete a set of records based on search criteria.
<PREFIX>DrillDown	Get a list of distinct column values based on search criteria.
<PREFIX>Export	Export records from the table based on search criteria.
<PREFIX>Get	Get a single record from the table.
<PREFIX>GetList	Get a list of records based on search criteria.
<PREFIX>GetStats	Get summarized or calculated information from a table based on search criteria. Several possibilities include counting records, summing or averaging values.
<PREFIX>Update	Update a single record in the table.

<PREFIX>

Each procedure is uniquely named because Microsoft SQL Server (Transact-SQL) does not have the concept of a package. Each stored procedure name is prefixed with:

p<APP NAME><TABLE>

<APP NAME> is the name you assigned to the application when you created it.

<TABLE> is the name of the table upon which the procedure operates.

For example, the Add procedure for an Employee table in an application whose name is HR is named:

PHREmployeeAdd

Please note that if two developers are building applications using the same physical database on the same server, then they must use different application names for their respective applications. If the two names are the same, possibly because they are both working on the same application, then the stored procedures will have the same names and will be constantly overwriting each other's stored procedures. Iron Speed Designer does not generate Stored Procedures for dynamic queries used by QuickSelector, Charts and aggregate controls and uses instead inline code. If your application requires only using stored procedures without exceptions do not use these controls.

Parameter Restrictions

There is a 4K limit on parameters passed to stored procedures in Microsoft SQL Server.

See

Add Transact-SQL Stored Procedure

Delete Transact-SQL Stored Procedure

DeleteRecords Transact-SQL Stored Procedure

DrillDown Transact-SQL Stored Procedure

Export Transact-SQL Stored Procedure

Get Transact-SQL Stored Procedure

GetList Transact-SQL Stored Procedure

GetStats Transact-SQL Stored Procedure

Update Transact-SQL Stored Procedure

Add Transact-SQL Stored Procedure

This Transact-SQL stored procedure inserts a single record in the table.

Parameter Name

Type

Description

p_c1
p_c2
etc.

Depends on the field

The column values for the record. One column value is provided for each field in the database table.

p_id

INTEGER

Output

Primary key value for the newly created record generated by a database sequence.

Delete Transact-SQL Stored Procedure

This Transact-SQL stored procedure deletes a single record from the table.

Parameter Name	Type	Description
p_id	INTEGER	Primary key value for the record to be deleted.

DeleteRecords Transact-SQL Stored Procedure

This Transact-SQL stored procedure deletes a set of records based on search criteria.

Parameter Name

Type

Description

p_search_str

VARCHAR2

The criteria used to create the WHERE clause that selects the records to be deleted.

p_num_deleted

INTEGER

Output

The number of records deleted.

DrillDown Transact-SQL Stored Procedure

This Transact-SQL stored procedure fetches a list of distinct column values based on search criteria.

Parameter Name	Type	Description
p_select_str	VARCHAR2	The field names to be returned. This forms the SELECT clause in the SQL query.
p_from_str	VARCHAR2	The additional tables with which a join operation is performed. This forms the FROM clause in the SQL query.
p_where_str	VARCHAR2	The search criteria forming the WHERE clause of the SQL query.
p_sort_str	VARCHAR2	The sorting criteria forming the ORDER BY clause of the SQL query.
p_list	Cursor Output	A cursor containing the result set of the SQL query.

Export Transact-SQL Stored Procedure

This Transact-SQL stored procedure exports records from the table based on search criteria.

Parameter Name	Type	Description
p_title_str	VARCHAR2	The column headings to be placed in the file’s header row..
p_select_str	VARCHAR2	The field names to be returned. This forms the SELECT clause in the SQL query.
p_from_str	VARCHAR2	The additional tables with which a join operation is performed. This forms the FROM clause in the SQL query.
p_search_str	VARCHAR2	The search criteria forming the WHERE clause of the SQL query.
p_clob	CLOB Output	The contents of the file being exported.
p_num_exported	NUMBER Output	The number of records exported.

Get Transact-SQL Stored Procedure

This Transact-SQL stored procedure fetches a single record from the table.

Parameter Name

Type

Description

p_id

INTEGER

The primary key of the record to be fetched.

p_cur

CURSOR

Output

A cursor pointing to the record being fetched.

GetList Transact-SQL Stored Procedure

This Transact-SQL stored procedure fetches a list of records based on search criteria.

Parameter Name	Type	Description
p_from_str	VARCHAR2	The additional tables with which a join operation is performed. This forms the FROM clause in the SQL query.
p_search_str	VARCHAR2	The search criteria forming the WHERE clause of the SQL query.
p_sort_str	VARCHAR2	The sorting criteria forming the ORDER BY clause of the SQL query.
p_page_number	INTEGER	The page within the result set whose data is to be returned.
p_batch_size	INTEGER	The total number of records to be displayed in a page (table grid).
p_total_size	INTEGER Output	The total number of records in the result set of the SQL query.
p_list	Cursor Output	A cursor containing the result set of the SQL query.

GetStats Transact-SQL Stored Procedure

This Transact-SQL stored procedure gets summarized or calculated information from a table based on search criteria. Several possibilities include counting records, summing and averaging values.

Parameter Name	Type	Description
p_select_str	VARCHAR2	The field names to be returned. This forms the SELECT clause in the SQL query.
p_from_str	VARCHAR2	The additional tables with which a join operation is performed. This forms the FROM clause in the SQL query.
p_search_str	VARCHAR2	The search criteria forming the WHERE clause of the SQL query.
p_sort_str	VARCHAR2	The sorting criteria forming the ORDER BY clause of the SQL query.
p_page_number	INTEGER	The page within the result set whose data is to be returned.
p_batch_size	INTEGER	The total number of records to be displayed in a page (table grid).
p_list	Cursor Output	A cursor containing the result set of the SQL query.

Update Transact-SQL Stored Procedure

This Transact-SQL stored procedure updates a single record in the table.

Parameter Name	Type	Description
p_id	INTEGER	Primary key value for the record being updated.
p_c1 p_c2 etc.	Depends on the field	The field values being updated in the record. There is one field value for each field in the table.