create procedure

Description

Creates a stored procedure or an extended stored procedure (ESP) that can take one or more user-supplied parameters.

Examples

Example 1

Given a table name, the procedure showind displays its name and the names and identification numbers of any indexes on any of its columns:

create procedure showind @tabname varchar (30) 
as 
  select sysobjects.name, sysindexes.name, indid 
  from sysindexes, sysobjects 
  where sysobjects.name = @tabname 
  and sysobjects.id = sysindexes.id

Here are the acceptable syntax forms for executing showind:

execute showind titles

execute showind @tabname = "titles"

Or, if this is the first statement in a file or batch:

showind titles

Example 2

This procedure displays information about the system tables if the user does not supply a parameter:

create procedure 
showsysind @table varchar (30) = "sys%" 
as 
  select sysobjects.name, sysindexes.name, indid 
  from sysindexes, sysobjects 
  where sysobjects.name like @table 
  and sysobjects.id = sysindexes.id

Example 3

This procedure specifies an action to be taken if the parameter is NULL (that is, if the user does not give a parameter):

create procedure 
showindnew @table varchar (30) = null 
as 
  if @table is null 
   print "Please give a table name" 
  else 
   select sysobjects.name, sysindexes.name, indid 
   from sysindexes, sysobjects 
   where sysobjects.name = @table 
   and sysobjects.id = sysindexes.id

Example 4

This procedure multiplies two integer parameters and returns the product in the output parameter, @result:

create procedure mathtutor @mult1 int, @mult2 int, 
  @result int output 
as 
select @result = @mult1 * @mult2

• If you use the as external name syntax, create procedure registers an extended stored procedure (ESP). Extended stored procedures execute procedural language functions rather than Transact-SQL commands.

• (Windows) An ESP function should not call a C runtime signal routine. This can cause XP Server to fail, because Open Server™ does not support signal handling on Windows NT.

• To support multithreading, ESP functions should use the Open Server srv_yield function, which suspends and reschedules the XP Server thread to allow another thread of the same or higher priority to execute.

• The DLL search mechanism is platform-dependent. On Windows NT, the sequence of a DLL file name search:

  1. The directory from which the application is loaded

  2. The current directory

  3. The system directory (SYSTEM32)

  4. Directories listed in the PATH environment variable

  If the DLL is not in the first three directories, set the PATH to include the directory in which it is located.

  On UNIX platforms, the search method varies with the particular platform. If it fails to find the DLL or shared library, it searches $SYBASE/lib.

  Absolute path names are not supported.

• System administrators can create new system procedures in the sybsystemprocs database. System procedure names must begin with the characters “sp_”. You can execute these procedures from any database by specifying the procedure name; you need not qualify it with the sybsystemprocs database name. For more information about creating system procedures, see the System Administration Guide.

• System procedure results may vary, depending on the context in which they are executed. For example, sp_foo, which executes the db_name () system function, returns the name of the database from which it is executed. When executed from the pubs2 database, it returns the value “pubs2”:

  use pubs2
  sp_foo
  

  ------------------------------ 
  pubs2 
  

  When executed from sybsystemprocs, it returns the value “sybsystemprocs”:

  use sybsystemprocs
  sp_foo
  

  ------------------------------ 
  sybsystemprocs 
  

• Procedure nesting occurs when one stored procedure calls another.

• If you execute a procedure that calls another procedure, the called procedure can access objects created by the calling procedure.

• The nesting level increments when the called procedure begins execution, and decrements when the called procedure completes execution. Exceeding the maximum of 16 levels of nesting causes the transaction to fail.

• You can call another procedure by name or by a variable name in place of the actual procedure name.

• The current nesting level is stored in the @@nestlevel global variable.

• Stored procedures can return an integer value called a return status. The return status either indicates that the procedure executed successfully or specifies the type of error that occurred.

• When you execute a stored procedure, it automatically returns the appropriate status code. Adaptive Server currently returns these status codes:

  Code	Meaning
  0	Procedure executed without error
  -1	Missing object
  -2	Datatype error
  -3	Process was chosen as deadlock victim
  -4	Permission error
  -5	Syntax error
  -6	Miscellaneous user error
  -7	Resource error, such as out of space
  -8	Nonfatal internal problem
  -9	System limit was reached
  -10	Fatal internal inconsistency
  -11	Fatal internal inconsistency
  -12	Table or index is corrupt
  -13	Database is corrupt
  -14	Hardware error

  Codes -15 through -99 are reserved for future use.

• Users can generate a user-defined return status with the return statement. The status can be any integer other than 0 through -99. The following example returns 1 when a book has a valid contract and 2 in all other cases:

  create proc checkcontract @titleid tid 
  as 
  if (select contract from titles where 
          title_id = @titleid) = 1 
     return 1 
  else 
     return 2
  

  checkcontract @titleid = "BU1111"
  

   (return status = 1)
  

  checkcontract @titleid = "MC3026"
  

   (return status = 2)
  

• If more than one error occurs during execution, the code with the highest absolute value is returned. User-defined return values take precedence over system-defined values.

• You can create a procedure to reference a temporary table if the temporary table is created in the current session. A temporary table created within a procedure disappears when the procedure exits. See the Transact-SQL Users Guide.

• System procedures such as sp_help work on temporary tables, but only if you use them from tempdb.

You can use the set command inside a stored procedure. Most set options remain in effect during the execution of the procedure, then revert to their former settings.

• For a report on the objects referenced by a procedure, use sp_depends.

• To display the text of a create procedure statement, which is stored in syscomments, use sp_helptext with the procedure name as the parameter. You must be using the database where the procedure resides when you use sp_helptext. To display the text of a system procedure, execute sp_helptext from the sybsystemprocs database.

• To see a list of system extended stored procedures and their supporting DLLs, use sp_helpextendedproc from the sybsystemprocs database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create procedure permission defaults to the database owner, who can transfer it to other users.

• If the procedure’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the procedure performs a select from a table that the user cannot access, the procedure execution fails.

• If a procedure and its objects are owned by the same user, however, special rules apply. The invoker automatically has “implicit permission” to access the procedure’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the procedure might be able to access only certain rows and columns of your table.

  A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also