Creates a new procedure in the database in a manner compatible with Adaptive Server Enterprise.
The following subset of the Transact-SQL CREATE PROCEDURE statement is supported in Sybase IQ.
CREATE PROCEDURE [ owner.]procedure_name … [ [ ( ] @parameter_name data-type [ = default ] [ OUTPUT ] [ , … ] [ ) ] ] …[ WITH RECOMPILE ] … AS … statement-list
The following differences between Transact-SQL and Sybase IQ statements are listed to help those writing in both dialects.
Variable names prefixed by @ The “@” sign denotes a Transact-SQL variable name, while 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, 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 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, while 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 Departmens, Employees WHERE Departments.DepartmentName = @deptname AND Departments.DepartmentID = Employees.DepartmentID
The following is 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.
There are two ways to create stored procedures: T-SQL
and SQ/92. BEGIN TRANSACTION, for example,
is T-SQL specific when using CREATE PROCEDURE syntax.
Do not mix syntax when creating stored procedures.
Automatic commit.
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.
Must have RESOURCE authority.
“Copy Definition utility (defncopy)” in Chapter 3, “Database Administration Utilities” of the Utility Guide
