CREATE PROCEDURE Statement [T-SQL]

Quick Links:

Syntax

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

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

Parameters

(back to top)

CREATE creates a new procedure.

OR REPLACE replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions.

Usage

(back to top)

Differences between Transact-SQL and SAP IQ SQL statements:

Variable names prefixed by @ the “@” sign denotes a Transact-SQL variable name; SAP IQ variables can be any valid identifier and the @ prefix is optional.
Input and output parameters SAP 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 IQ should be declared with OUTPUT in Transact-SQL.
Parameter default values SAP 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 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 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 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

(back to top)

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
SAP Database products—SAP IQ supports a subset of the SAP ASE CREATE PROCEDURE statement syntax.
If the Transact-SQL WITH RECOMPILE optional clause is supplied, it is ignored. SAP 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

(back to top)

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.