create procedure

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 | execute as {owner | caller}} ]
	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 that share the same name so 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, and larger for identifiers. 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 47 in Chapter 1, “System and User-Defined Datatypes”of Reference Manual: Building Blocks. Stored procedure parameters cannot have a datatype of text, unitext, or image or a user-defined datatype whose underlying type is text, unitext, 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.

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 creates a new plan each time the procedure is executed. Use this optional clause when you expect that the execution of a procedure is atypical—that is, when you need a new plan. The with recompile clause has no impact on the execution of an extended stored procedure.

with execute as

specifies whether to execute the procedure as the owner or the caller. When executed as the owner, all actions in the procedure are checked against the privileges of the procedure owner. When executed as the caller, all actions in the procedure are checked against the privileges of the procedure caller.

owner

checks runtime permissions, executes DDL, and resolves objects names on behalf of the procedure owner. execute as definer is also supported as an alternative syntax.

caller

checks runtime permissions, executes DDL, and resolves objects names on behalf of the procedure caller. execute as invoker is also supported as an alternative syntax.

SQL_statements

specifies the actions the procedure is to take. You can include any number and kind of SQL statements, 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. 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.

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

execute showind titles

execute showind @tabname = "titles"

showind titles

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

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

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

If the procedure is executed by passing it three 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

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

• The value of the return parameter is always reported, whether or not its value has changed.

• @result does not need to be declared in the calling batch because it is the name of a parameter to be passed to mathtutor.

• Although the changed value of @result is returned to the caller in the variable assigned in the execute statement (in this case, @guess), it appears under its own heading (@result).

Example 6

You can use return parameters 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"

Example 8

Creates a procedure using the execute as owner clause. Jane creates the procedure and Bill requires only execute permission to run the procedure. The table emp_interim is created and owned by Jane. If Jane does not have create table permission, the procedure will fail:

create procedure p_emp  

    with execute as owner as

    select * into emp_interim
    from jane.employee

grant execute on p_emp to bill

Example 9

Creates a procedure using the execute as caller clause. Jane creates the procedure and Bill requires execute permission to run the procedure. Jane owns both p_emp and jane.employee. Bill will require select permission on jane.employee. The table emp_interim is created and owned by Bill. Bill must have create table permission:

create procedure p_emp  

    with execute as caller as

    select * into emp_interim
    from jane.employee

grant execute on p_emp to bill

Example 10

Creates a procedure using the execute as owner clause with references to an object with an unqualified name. Jane creates the procedure and Bill executes the procedure. Adaptive Server will search for a table named t1 owned by Jane. If jane.t1 does not exist, Adaptive Server will look for dbo.t1. If Adaptive Server resolves t1 to dbo.t1, permission to insert into t1 must have been granted to Jane:

create procedure insert p  

    with execute as owner as

    insert t1 (c1) values (100)

grant execute on insert p to bill

Example 11

Creates a procedure using the execute as caller clause with references to an object with an unqualified name. Jane creates the procedure and Bill executes the procedure. Adaptive Server will search for a table named t1 owned by Bill. If bill.t1 does not exist, Adaptive Server will look for dbo.t1. If Adaptive Server resolves t1 to dbo.t1, permission to insert into t1 must have been granted to Bill:

create procedure insert p  

    with execute as caller as

    insert t1 (c1) values (100)

grant execute on insert p to bill

Example 12

Creates a procedure using the execute as owner clause that invokes a nested procedure in another database with a fully qualified name. Jane creates the procedure and Bill executes the procedure. The login associated with Jane resolves to user Jane in otherdb. Adaptive Server checks that user Jane in otherdb has execute permission on jim.p_child. If jim.p_child has been created with execute as owner then p_child will be executed on behalf of Jim. If jim.p_child has been created with execute as caller or without the execute as clause, then p_child will execute on behalf of Jane:

create procedure p master  

    with execute as owner 

    as exec otherdb.jim.p_child

grant execute p master to bill

Example 13

Creates a procedure using the execute as caller clause that invokes a nested procedure in another database with a fully qualified name. Jane creates the procedure and Bill executes the procedure. The login associated with Bill resolves to user Bill in otherdb. Adaptive Server checks that user Bill in otherdb has execute permission on jim.p_child. If jim.p_child has been created with execute as owner then p_child will be executed on behalf of Jim. If jim.p_child has been created with execute as caller or without the execute as clause, then p_child will execute on behalf of Bill:

create procedure p master  

    with execute as caller

    as exec otherdb.jim.p_child

grant execute on p master to bill

Usage

• To avoid seeing unexpected results due to changes in settings, run set rowcount 0 as your initial statement before executing create procedure. The scope of set is limited to the create procedure command, and resets to your previous setting once the procedure exits.

• After a procedure is created, you can run it by issuing the execute command along with the procedure’s name and any parameters. If a procedure is the first statement in a batch, you can give its name without the keyword execute.

• You can use sp_hidetext to hide the source text for a procedure, which is stored in syscomments.

• When a stored procedure batch executes successfully, Adaptive Server sets the @@error global variable to 0.

Restrictions

• The maximum number of parameters that a stored procedure can have is 2048.

• The maximum number of local and global variables in a procedure is limited only by available memory.

• The maximum amount of text in a stored procedure is 16MB.

• You cannot combine a create procedure statement with other statements in a single batch.

• You can create a stored procedure only in the current database, although the procedure can reference objects from other databases. Most objects referenced in a procedure must exist at the time you create the procedure. However, you can include statements like drop table, create index, or truncate table. These are allowed in a create procedure statement even if the underlying object does not exist when you create the procedure.

  You can create an object within a procedure, then reference it, provided the object is created before it is referenced.

  You cannot use alter table in a procedure to add a column and then refer to that column within the procedure.

• If you use select * in your create procedure statement, the procedure (even if you use the with recompile option to execute) does not pick up any new columns you may have added to the table. You must drop the procedure and re-create it. Otherwise, the wrong results can be caused by the insert...select statement of insert into table1 select * from table2 in the procedure when new columns have been added to both tables.

• Within a stored procedure, you cannot create an object (including a temporary table), drop it, then create a new object with the same name. Adaptive Server creates the objects defined in a stored procedure when the procedure is executed, not when it is compiled.

  WARNING! Certain changes to databases, such as dropping and re-creating indexes, can cause object IDs to change. When object IDs change, stored procedures recompile automatically, and can increase slightly in size. Leave some space for this increase.

Extended stored procedures

execute as stored procedure

System procedures

Nested procedures

Procedure return status

Object identifiers

• To change the name of a stored procedure, use sp_rename.

• To change the name of an extended stored procedure, drop the procedure, rename and recompile the supporting function, then re-create the procedure.

• If a procedure references table names, column names, or view names that are not valid identifiers, you must set quoted_identifier on before the create procedure command and enclose each such name in double quotes. The quoted_identifier option does not need to be on when you execute the procedure.

• You must drop and re-create the procedure if any of the objects it references have been renamed.

• Inside a stored procedure, object names used with the create table and dbcc commands must be qualified with the object owner’s name if other users are to make use of the stored procedure. For example, user “mary,” who owns the table marytab, should qualify the name of her table inside a stored procedure (when it is used with these commands) if she wants other users to be able to execute it. This is because the object names are resolved when the procedure is run. When another user tries to execute the procedure, Adaptive Server looks for a table called marytab owned by the user “mary” and not a table called marytab owned by the user executing the stored procedure.

  Thus, if marytab is not qualified, and user “john” tries to execute the procedure, Adaptive Server looks for a table called marytab owned by the owner of the procedure (“mary,” in this case) or by the database owner if the user table does not exist. For example, if the table mary.marytab is dropped, the procedure references dbo.marytab.

  Object names used with other statements (for example, select or insert) inside a stored procedure need not be qualified because the names are resolved when the procedure is compiled.

Temporary tables and procedures

Setting options in procedures

However, if you use a set option (such as identity_insert) which requires the user to be the object owner, a user who is not the object owner cannot execute the stored procedure.

Getting information about procedures

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, sp_help

---

Copyright © 2012. Sybase Inc. All rights reserved.

View this document as PDF