User's Guide

Part II. Tutorials

Chapter 4. Managing Databases with Sybase Central

Stored procedures are kept in a folder within the database container object. This tutorial shows how to view and alter the contents of a procedure, and how to create new procedures using the Sybase Central code editor.

The Sybase Central code editor is a separate window for displaying and editing the code of triggers, procedures, and views.

Beyond text-editing functions, it provides:

• Automatic syntax highlighting.
• Automatic formulation of DDL statements.
• Unlimited Undo and Redo.
• Ability to find and replace text, and to jump to specific line numbers.
• Ability to execute the DDL code against the database.

Viewing stored procedure code with Sybase Central

There are several stored procedures included in the sample database. The following steps explain how to view and edit stored procedures.

To view the contents of a procedure:

1. Select the Procedures & Functions folder from the sample database in the left panel. You may have to expand the database container to do this.
2. In the right panel, double-click sp_customer_list in the right panel. The Sybase Central code editor appears, displaying the text of the procedure.
3. A call to this procedure returns a set of customer IDs and company names from the Customer table.

Viewing procedures in Watcom-SQL or Transact-SQL

SQL Anywhere supports two alternative syntaxes for stored procedures. The native SQL Anywhere syntax (Watcom-SQL) is based on the ISO draft standard. SQL Anywhere also supports the Sybase Transact-SQL syntax. You can enter procedures in either syntax, and SQL Anywhere can automatically translate between the two syntaxes.

Not all procedure statements may translate. Untranslated statements appear as comments in the translated procedure.

To view the alternative syntaxes of a procedure:

1. With the right mouse button, click on the sp_customer_list procedure. A pop-up menu appears.
2. Click Open to view the procedure in the syntax in which it was entered. This is the syntax in which the procedure is stored in the database.
3. Click Open as Watcom-SQL to view the procedure in Watcom-SQL syntax.
4. Click Open as Transact-SQL to view the procedure in Transact-SQL syntax.

Notes

• The Sybase Central code editor is used to display and create views and triggers as well as stored procedures.

Setting permissions on procedures

Stored procedures have permissions associated with them. In order to execute a procedure you either need to be granted permission to execute a procedure, or you need to be a member of a user group that has permission to execute the procedure.

To view and alter the permissions on a procedure:

1. Using the right mouse button, click on the sp_customer_list procedure. A pop-up menu appears.
2. Select Properties from this menu. The properties sheet for sp_customer_list appears.
3. Click the Permissions tab to see which user IDs have been granted permissions on this procedure. Currently none do, as the only user for the sample database is DBA, who automatically has execute permissions as owner of the procedure.
4. Click the Grant Permissions button tab to grant users or groups permission to execute this procedure. Grant permission to execute this procedure to the Public user group by selecting the Public user group icon and clicking the Grant Permission button.
5. Click the OK button to accept the changes to the sp_customer_list permissions or Cancel to undo the changes.