Microsoft SQL Server Database stored procedures
Retrieval and update
One of the most significant features of SQL Server is database
stored procedures. You can use database stored procedures for:
-
Retrieval only
-
Update only
-
Update and retrieval
PowerBuilder supports all these uses in PowerBuilder embedded
SQL.
Using AutoCommit with database stored
procedures
Database stored procedures often create temporary table that hold
rows accumulated during processing. To create these tables, the stored
procedure executes SQL Data Definition Language (DDL) statements. Versions
of SQL Server prior to SQL Server 2000 do not allow you to execute DDL
statements within the scope of a transaction.
To execute SQL Server stored procedures that contain DDL statements
statements in SQL Server 7 and earlier, you must set the AutoCommit
property of the transaction object to true so PowerBuilder issues the
statements outside the scope of a transaction. However, if AutoCommit is
set to true, you cannot issue a ROLLBACK. Therefore, you should set
AutoCommit back to false (the default) immediately after completing the
DDL operation.
When you change the value of AutoCommit from false to true,
PowerBuilder issues a COMMIT statement by default.
System database stored
procedures
You can access system database stored procedures the same way you
access user-defined stored procedures. You can use the DECLARE statement
against any procedure and can qualify procedure names if necessary.
See also
Microsoft SQL Server
Retrieval
Microsoft SQL Server
Temporary tables
Microsoft SQL Server Using
database stored procedures in DataWindow objects