Creates a stored procedure or an extended stored procedure (ESP) that can take one or more user-supplied parameters.
For syntax and usage information about the SQLJ command
for creating procedures, see create function (SQLJ).
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}
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.
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.
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.
is the datatype of the parameter. See “User-defined datatypes” on page 43 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, 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.
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).
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.
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 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.
specify the actions the procedure is to take. You can include nny 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.
creates an extended stored procedure. You cannot use the number parameter with as external 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.
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
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
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
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)
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
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 is displayed under its own heading (@result).
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!
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"
To avoid seeing unexpected results due to changes in settings, run set rowcount 0 as your initial statement before exeucting create procedure. The scope of set is limited to just your 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.
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.
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.
On Windows NT – 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 is as follows:
The directory from which the application is loaded
The current directory
The system directory (SYSTEM32)
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_”. These procedures can be executed from any database by specifying the procedure name; it is not necessary to 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 the following 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 |
Non-fatal 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.
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.
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. For more information, see the Transact-SQL User’s 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.
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.
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.
ANSI SQL – Compliance level: Transact-SQL extension.
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.
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.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
11 |
create |
create procedure |
|
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
