create plan  create procedure (SQLJ)

Chapter 1: Commands

create procedure

Description

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

NoteFor syntax and usage information about the SQLJ command for creating procedures, see create function (SQLJ).

Syntax

create procedure [owner.]procedure_name[;number]
	[[(]@parameter_name 
		datatype [(length ) | (precision  [, scale ])]
		[= default][output]
	[, @parameter_name 
		datatype [(length ) | (precision  [, scale ])]
		[= default][output]]...[)]]
	[with recompile] 
	as {SQL_statements | external name dll_name}

Parameters

procedure_name

is the name of the procedure. It must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another procedure of the same name owned by a different user in the current database. The default value for owner is the current user.

;number

is an optional integer used to group procedures of the same name so that they can be dropped together with a single drop procedure statement. Procedures used in the same application are often grouped this way. For example, if the procedures used with the application named orders are named orderproc;1, orderproc;2, and so on, the following statement drops the entire group:

 drop proc orderproc

Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, the following statement is not allowed:

 drop procedure orderproc;2

You cannot group procedures if you are running Adaptive Server in the evaluated configuration. The evaluated configuration requires that you disallow procedure grouping so that every stored procedure has a unique object identifier and can be dropped individually. To disallow procedure grouping, a System Security Officer must use sp_configure to reset allow procedure grouping. For more information about the evaluated configuration, see the System Administration Guide.

parameter_name

is the name of an argument to the procedure. The value of each parameter is supplied when the procedure is executed. Parameter names are optional in create procedure statements—a procedure is not required to take any arguments.

Parameter names must be preceded by the @ sign and conform to the rules for identifiers. A parameter name, including the @ sign, can be a maximum of 30 characters. Parameters are local to the procedure: the same parameter names can be used in other procedures.

If the value of a parameter contains nonalphanumeric characters, it must be enclosed in quotes. This includes object names qualified by a database name or owner name, since they include a period. If the value of a character parameter begins with a numeric character, it also must be enclosed in quotes.

datatype[(length) | (precision [, scale])]

is the datatype of the parameter. See “User-defined datatypes” on page 44 in Chapter 1, “System and User-Defined Datatypes”of Reference Manual: Building Blocks. for more information about datatypes. Stored procedure parameters cannot have a datatype of text or image or a user-defined datatype whose underlying type is text or image.

The char, varchar, unichar, univarchar, nchar, nvarchar, binary, and varbinary datatypes should include a length in parentheses. If you omit the length, Adaptive Server truncates the parameter value to 1 character.

The float datatype expects a binary precision in parentheses. If you omit the precision, Adaptive Server uses the default precision for your platform.

The numeric and decimal datatypes expect a precision and scale, enclosed in parentheses and separated by a comma. If you omit the precision and scale, Adaptive Server uses a default precision of 18 and a scale of 0.

default

defines a default value for the procedure’s parameter. If a default is defined, a user can execute the procedure without giving a parameter value. The default must be a constant. It can include the wildcard characters (%, _, [ ], and [^]) if the procedure uses the parameter name with the keyword like (see Example 2).

The default can be NULL. The procedure definition can specify that some action be taken if the parameter value is NULL (see Example 3).

output

indicates that the parameter is a return parameter. Its value can be returned to the execute command that called this procedure. Use return parameters to return information to the calling procedure (see Example 5).

To return a parameter value through several levels of nested procedures, each procedure must include the output option with the parameter name, including the execute command that calls the highest level procedure.

The output keyword can be abbreviated to out.

with recompile

means that Adaptive Server never saves a plan for this procedure; a new plan is created each time it is executed. Use this optional clause when you expect that the execution of a procedure will be atypical—that is, when you need a new plan. The with recompile clause has no impact on the execution of an extended stored procedure.

SQL_statements

specify the actions the procedure is to take. Any number and kind of SQL statements can be included, with the exception of create view, create default, create rule, create procedure, create trigger, and use.

create procedure SQL statements often include control-of-flow language, including one or more of the following: declare; if...else; while; break; continue; begin...end; goto label; return; waitfor; /* comment */. They can also refer to parameters defined for the procedure.

The SQL statements can reference objects in another database, as long as they are properly qualified.

external name

creates an extended stored procedure. If the as external name syntax is used, you cannot use the number parameter with as external name.

dll_name

specifies the name of the dynamic link library (DLL) or shared library containing the functions that implement the extended stored procedure. The dll_name can be specified with no extension or with a platform-specific extension, such as .dll on Windows NT or .so on Sun Solaris. If you specify the extension, enclose the entire dll_name in quotation marks.

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 the procedure is executed by passing it 3 integers, the select statement performs the multiplication and assigns the values, but does not print the return parameter:

mathtutor 5, 6, 32

(return status 0)

Example 5

In this example, both the procedure and the execute statement include output with a parameter name so that the procedure can return a value to the caller:

declare @guess int 
select @guess = 32 
exec mathtutor 5, 6, @result = @guess output 

(1 row affected) 
(return status = 0) 
 
Return parameters: 
 
@result      
-----------  
         30

The output parameter and any subsequent parameters in the execute statement, @result, must be passed as:

     @parameter = value

Example 6

Return parameters can be used in additional SQL statements in the batch or calling procedure. This example shows how to use the value of @guess in conditional clauses after the execute statement by storing it in another variable name, @store, during the procedure call. When return parameters are used in an execute statement that is part of a SQL batch, the return values are printed with a heading before subsequent statements in the batch are executed.

declare @guess int 
declare @store int 
select @guess = 32 
select @store = @guess 
execute mathtutor 5, 6, @result = @guess output 
select Your_answer = @store, Right_answer = @guess 
if @guess = @store 
    print "Right-o" 
else 
    print "Wrong, wrong, wrong!"

(1 row affected) 
(1 row affected) 
(return status = 0) 

Return parameters: 
 
@result      
-----------  
         30 
Your_answer Right_answer  
----------- ------------  
         32           30  
                           
(1 row affected) 
Wrong, wrong, wrong!

Example 7

Creates an extended stored procedure named xp_echo, which takes an input parameter, @in, and echoes it to an output parameter, @out. The code for the procedure is in a function named xp_echo, which is compiled and linked into a DLL named sqlsrvdll.dll:

create procedure xp_echo @in varchar(255),
        @out varchar(255) output
as external name "sqlsrvdll.dll"

Usage


Restrictions


Extended stored procedures


System procedures


Nested procedures


Procedure return status


Object identifiers


Temporary tables and procedures


Setting options in procedures


Getting information about procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create procedure permission defaults to the Database Owner, who can transfer it to other users.

Permission to use a procedure must be granted explicitly with the grant command and may be revoked with the revoke command.

Permissions on objects at procedure creation When you create a procedure, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the procedure. Therefore, you can create a procedure successfully even though you do not have access to its objects. All permission checks occur when a user executes the procedure.

Permissions on objects at procedure execution When the procedure is executed, permission checks on objects depend upon whether the procedure and all referenced objects are owned by the same user.

See also

Commands begin...end, break, continue, declare, drop procedure, execute, goto label, grant, if...else, return, select, waitfor, while

System procedures sp_addextendedproc, sp_helpextendedproc, sp_helptext, sp_hidetext, sp_rename




Copyright © 2004. Sybase Inc. All rights reserved. create procedure (SQLJ)

View this book as PDF