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 Update

Microsoft SQL Server Using database stored procedures in DataWindow objects

Microsoft SQL Server Database stored procedures summary