ASA SQL Reference
  SQL Statements

CREATE PROCEDURE statement [T-SQL]

Description 

Use this statement to create a new procedure in the database in a manner compatible with Adaptive Server Enterprise.

Syntax 1 

The following subset of the Transact-SQL CREATE PROCEDURE statement is supported in Adaptive Server Anywhere.

CREATE PROCEDURE [owner.]procedure_name
[ [ ( ] @parameter_name data-type [ = default ] [ OUTPUT ], ... [ ) ] ]
[ WITH RECOMPILE ] AS statement-list

Usage 

The following differences between Transact-SQL and Adaptive Server Anywhere statements (Watcom-SQL) are listed to help those writing in both dialects.

• Variable names prefixed by @    The "@" sign denotes a Transact-SQL variable name, while Watcom-SQL variables can be any valid identifier, and the @ prefix is optional.

• Input and output parameters    Watcom-SQL procedure parameters are specified as IN, OUT, or INOUT, while Transact-SQL procedure parameters are INPUT parameters by default or can be specified as OUTPUT. Those parameters that would be declared as INOUT or as OUT in Adaptive Server Anywhere should be declared with OUTPUT in Transact-SQL.

• Parameter default values    Watcom-SQL procedure parameters are given a default value using the keyword DEFAULT, while Transact-SQL uses an equality sign (=) to provide the default value.

• Returning result sets    Watcom-SQL uses a RESULT clause to specify returned result sets. In Transact-SQL procedures, the column names or alias names of the first query are returned to the calling environment.

  The following Transact-SQL procedure illustrates how result sets are returned from Transact-SQL stored procedures:

  CREATE PROCEDURE showdept @deptname varchar(30)
  AS
     SELECT employee.emp_lname, employee.emp_fname
     FROM department, employee
     WHERE department.dept_name = @deptname
     AND department.dept_id = employee.dept_id

  The following is the corresponding Watcom-SQL procedure:

  CREATE PROCEDURE showdept(in deptname
           varchar(30) )
  RESULT ( lastname char(20), firstname char(20))
  ON EXCEPTION RESUME
  BEGIN
     SELECT employee.emp_lname, employee.emp_fname
     FROM department, employee
     WHERE department.dept_name = deptname
     AND department.dept_id = employee.dept_id
  END

• Procedure body    The body of a Transact-SQL procedure is a list of Transact-SQL statements prefixed by the AS keyword. The body of a Watcom-SQL procedure is a compound statement, bracketed by BEGIN and END keywords.

Permissions 

Must have RESOURCE authority.

Side effects 

Automatic commit.

See also 

CREATE PROCEDURE statement

Standards and compatibility 

• SQL/92    Transact-SQL extension.

• SQL/99    Transact-SQL extension.

• Sybase    Anywhere supports a subset of the Adaptive Server Enterprise CREATE PROCEDURE statement syntax.

  If the Transact-SQL WITH RECOMPILE optional clause is supplied, it is ignored. Adaptive Server Anywhere always recompiles procedures the first time they are executed after a database is started, and stores the compiled procedure until the database is stopped.

  Groups of procedures are not supported.