In this lesson, you will learn how to use Sybase WorkSpace to create stored procedures for an Adaptive Server Enterprise server database.
If necessary, select Window|Open Perspective|Database Development from the main menu bar to open the Database Development perspective.
In the Database Explorer view, right-click the Procedures & Functions folder (under the appropriate pubs sample database) and select New|Procedure.
The Create Stored Procedure Skeleton wizard displays.
In the Name field,
enter play_proc.
Optionally, you can click the Recompile check box or add comments. As you enter or change information in this wizard, its automatically updates the preview.
Click Next to continue.
Enter the parameters for this stored procedure.
On the parameters page, enter the following two parameters. You can manually enter the data type or select it from the drop-down list.
P1 – with a data type of char(10). Although char(10) does not appear on the drop-down list, you can manually enter it in the Data Type field.
P2 – with a data type of int.
Click Next to enter variables for this stored procedure.
You can manually enter the data type or select it from the drop-down list.
V1 – with a data type of char(10). Although char(10) does not appear on the drop-down list, you can manually enter it in the Data Type field.
V2 – with a data type of int.
Click Finish.
The wizard creates a skeleton of the stored procedure in the database and displays it in the SQL Editor.
We will now add some basic SQL code to demonstrate some Sybase WorkSpace features as well as add a #temp table, which you will view and modify in a later lesson.
Add the following SQL statements to the stored procedure after the Print statement.
create table #play_table (c1 char(10), c2 int)� select @V1 = @P1, @V2 = @P2 select @V1, @V2� insert #play_table values (@V1, @V2) � select * from #play_table
Save the stored procedure to the server; right-click anywhere in the SQL Editor window and select Save to Server from the context menu.
You will use this stored procedure in subsequent tutorials.
Look at the text at the bottom of the SQL Editor.
Notice how Sybase WorkSpace expands the * (asterisk) into the complete column list from the table.
Now look in the Database Explorer view under the Procedures & Functions folder to see that Sybase WorkSpace added the new stored procedure, play_proc, to the list.
Select File|Close on the main menu bar to close the editing pane.�
You can easily make changes to the stored procedure and save it back to the server.
