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
