CREATE PROCEDURE Statement [T-SQL]

Creates a new procedure that is compatible with Adaptive Server Enterprise.

Syntax

This subset of the Transact-SQL CREATE PROCEDURE statement is supported in Sybase IQ:

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

Usage

Differences between Transact-SQL and Sybase IQ SQL statements:

Specifying an CREATE OR REPLACE PROCEDURE creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions. Also, an error is returned if the procedure being replaced is already in use.
Variable names prefixed by @—The “@” sign denotes a Transact-SQL variable name; Sybase IQ variables can be any valid identifier and the @ prefix is optional.
Input and output parameters—Sybase IQ procedure parameters are specified as IN, OUT, or INOUT; Transact-SQL procedure parameters are INPUT parameters by default or can be specified as OUTPUT. Those parameters declared as INOUT or as OUT in Sybase IQ should be declared with OUTPUT in Transact-SQL.
Parameter default values—Sybase IQ procedure parameters are given a default value using the keyword DEFAULT; Transact-SQL uses an equality sign (=) to provide the default value.
Returning result sets—Sybase IQ 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:

CREATE PROCEDURE showdept @deptname varchar(30)
AS
  SELECT Employees.Surname, Employees.givenName
  FROM Departments, Employees
  WHERE Departments.DepartmentName = @deptname
  AND Departments.DepartmentID =
        Employees.DepartmentID

The corresponding Sybase IQ procedure:

CREATE PROCEDURE showdept(in deptname 
      varchar(30) )
RESULT ( lastname char(20), firstname char(20))
ON EXCEPTION RESUME
BEGIN
  SELECT Employees.SurName, Employees.GivenName
  FROM Departments, Employees
  WHERE Departments.DepartmentName = deptname
  AND Departments.DepartmentID =
        Employees.DepartmentID
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 Sybase IQ procedure is a compound statement, bracketed by BEGIN and END keywords.

Note: There are two ways to create stored procedures: T-SQL and SQL/92. BEGIN TRANSACTION, for example, is T-SQL specific when using CREATE PROCEDURE syntax. Do not mix syntax when creating stored procedures.

Side Effects

Automatic commit

Standards

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
Sybase—Sybase IQ 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. SQL 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.

Permissions

Must have RESOURCE authority.