CREATE PROCEDURE statement [T-SQL]

• OR REPLACE clause

  Specifying 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 privileges. An error is returned if you attempt to replace a procedure that is already in use.

• NO RESULT SET clause

  Declares that no result set is returned by this procedure. This is useful when an external environment needs to know that a procedure does not return a result set.

• WITH RECOMPILE clause

  This clause is accepted for Transact-SQL compatibility, but 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.

The following differences between Transact-SQL and SQL 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 INOUT by default or can be specified as IN, OUT, or INOUT. Transact-SQL procedure parameters are INPUT parameters by default. They can be specified as input/output with the addition of the OUTPUT keyword. There are no output-only parameters in the Transact-SQL dialect.

  When you use the Watcom SQL dialect to declare a parameter OUT, it is output-only. The mixing of dialects is not recommended because it can cause problems when the procedure declaration is unloaded and used to rebuild the database. If the procedure declaration is unloaded and used to rebuild the database, the rebuilt procedure declaration is in the Transact-SQL dialect, the OUTPUT keyword is used, and the parameter is input/output.

• 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 Employees.Surname, Employees.GivenName
     FROM Departments, Employees
     WHERE Departments.DepartmentName = @deptname
     AND Departments.DepartmentID = Employees.DepartmentID;

  The following is the corresponding Watcom SQL procedure:

  CREATE PROCEDURE showdept2(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 Watcom SQL procedure is a compound statement, bracketed by BEGIN and END keywords.

You must have the CREATE PROCEDURE privilege to create procedures owned by you.

You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT privilege to create procedures owned by others.

To replace an existing procedure, you must own the procedure or have one of the following:

• CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.

• CREATE ANY OBJECT and DROP ANY OBJECT system privileges.

• ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.

Automatic commit.

• ANSI/ISO SQL Standard

  Not in the standard.

• Transact-SQL

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

  Only Transact-SQL SQL procedures are supported in the SQL Anywhere Transact-SQL dialect. To create an external procedure you must use Watcom SQL syntax. Adaptive Server Enterprise does not support the NO RESULT SET clause. 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 Transact-SQL procedures are not supported in SQL Anywhere.