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 SAP 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 SAP Sybase IQ SQL statements:

Specifying a 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; SAP Sybase IQ variables can be any valid identifier and the @ prefix is optional.
Input and output parameters—SAP 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 SAP Sybase IQ should be declared with OUTPUT in Transact-SQL.
Parameter default values—SAP 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—SAP 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 SAP 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 an SAP 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—SAP 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

Watcom SQL or Transact SQL procedure to be owned by self – Requires CREATE PROCEDURE system privilege.

Watcom SQL or Transact SQL procedure to be owned by any user – Requires one of:

CREATE ANY PROCEDURE system privilege.
CREATE ANY OBJECT system privilege.

Remote procedure to be owned by self – Requires all of:

CREATE EXTERNAL REFERENCE system privilege.
CREATE PROCEDURE system privilege.

Remote procedure to be owned by any user – Requires CREATE EXTERNAL REFERENCE system privilege. Also requires one of:

CREATE ANY PROCEDURE system privilege.
CREATE ANY OBJECT system privilege.