execute

Description

Runs a procedure or dynamically executes Transact-SQL commands.

Syntax

[exec[ute]] [@return_status =]
	[[[server .]database.]owner.]procedure_name[;number] 
		[[@parameter_name =] value | 
			[@parameter_name =] @variable [output]
		[, [@parameter_name =] value | 
			[@parameter_name =] @variable [output]...]] 
	[with recompile]

exec[ute] ("string" | char_variable 
	[+ "string" | char_variable]...)

Parameters

execute | exec: is used to execute a stored procedure or an extended stored procedure (ESP). This keyword is necessary if there are multiple statements in the batch.

execute is also used to execute a string containing Transact-SQL.

@return_status: is an optional integer variable that stores the return status of a stored procedure. @return_status must be declared in the batch or stored procedure before it is used in an execute statement.

server: is the name of a remote server. You can execute a procedure on another Adaptive Server as long as you have permission to use that server and to execute the procedure in that database. If you specify a server name, but do not specify a database name, Adaptive Server looks for the procedure in your default database.

database: is the database name. Specify the database name if the procedure is in another database. The default value for database is the current database. You can execute a procedure in another database as long as you are its owner or have permission to execute it in that database.

owner: is the procedure owner’s name. Specify the owner’s name if more than one procedure of that name exists in the database. The default value for owner is the current user. The owner name is optional only if the database owner owns the procedure or if you own it.

procedure_name: is the name of a procedure defined with create procedure.

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 an application named orders are named orderproc;1, orderproc;2, and so on, the following statement drops the entire group:

drop proc orderproc

After procedures have been grouped, individual procedures within the group cannot be dropped. For example, you cannot execute the statement:

drop procedure orderproc;2

parameter_name: is the name of an argument to the procedure, as defined in create procedure. Parameter names must be preceded by the @ sign.

If the “@parameter_name = value” form is used, parameter names and constants need not be supplied in the order defined in create procedure. However, if this form is used for any parameter, it must be used for all subsequent parameters.

value: is the value of the parameter or argument to the procedure. If you do not use the “@parameter_name = value” form, you must supply parameter values in the order defined in create procedure.

@variable: is the name of a variable used to store a return parameter.

output: indicates that the stored procedure is to return a return parameter. The matching parameter in the stored procedure must also have been created with the keyword output.

The output keyword can be abbreviated to out.

with recompile: forces compilation of a new plan. Use this option if the parameter you are supplying is atypical or if the data has significantly changed. The changed plan is used on subsequent executions. Adaptive Server ignores this option when executing an extended system procedure.

Using execute procedure with recompile many times can adversely affect procedure cache performance. Since a new plan is generated every time you use with recompile, a useful performance plan may be pushed out of the cache if there is insufficient space for new plans.

string: is a literal string containing part of a Transact-SQL command to execute. There are no restrictions to the number of characters supplied with the literal string.

char_variable: is the name of a variable that supplies the text of a Transact-SQL command.

Examples

Example 1

All three statements execute showind with a parameter value titles:

execute showind titles

exec showind @tabname = titles

If this is the only statement in a batch or file:

showind titles

Example 2

Executes checkcontract on the remote server GATEWAY. Stores the return status indicating success or failure in @retstat:

declare @retstat int 
execute @retstat = GATEWAY.pubs.dbo.checkcontract "409-56-4008"

Example 3

Executes roy_check, passing three parameters. The third parameter, @pc, is an output parameter. After execution of the procedure, the return value is available in the variable @percent:

declare @percent int 
select @percent = 10 
execute roy_check "BU1032", 1050, @pc = @percent output 
select Percent = @percent

Example 4

This procedure displays information about the system tables if you do 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 5

Executes xp_echo, passing in a value of “Hello World!” The returned value of the extended stored procedure is stored in a variable named result:

declare @input varchar (12), @in varchar (12),
    @out varchar (255), @result varchar (255)
select @input="Hello World!"
execute xp_echo @in = @input, @out= @result output

Example 6

The final execute command concatenates string values and character variables to issue the Transact-SQL command:

select name from sysobjects where id=3

declare @tablename char (20)
declare @columname char (20)
select @tablename="sysobjects”
select @columname="name"
execute ('select ' + @columname + ' from ' + @tablename + ' where id=3')

Example 7

Executes sp_who:

declare @sproc varchar (255)
select @sproc = "sp_who"
execute @sproc

Usage

You can use execute with an archive database as long as any statements that reference the archive database are allowed within the archive database. A transaction inside or outside a stored procedure is not permitted with an execute command.
Procedure results may vary, depending on the database in which they are executed. For example, the user-defined system procedure 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”:
```
exec pubs2..sp_foo
```
```
------------------------------ 
pubs2
 (1 row affected, return status = 0)
```
When executed from sybsystemprocs, it returns the value “sybsystemprocs”:
```
exec sybsystemprocs..sp_foo
```
```
------------------------------ 
sybsystemprocs
 (1 row affected, return status = 0)
```
There are two ways to supply parameters—by position, or by using:
```
@parameter_name = value
```
If you use the second form, you need not supply the parameters in the order defined in create procedure.

If you are using the output keyword and intend to use the return parameters in additional statements in your batch or procedure, the value of the parameter must be passed as a variable. For example:
```
parameter_name = @variable_name
```
When executing an extended stored procedure, pass all parameters by either name or value. You cannot mix parameters by value and parameters by name in a single invocation of the execute command for an ESP.
The dynamic SQL syntax of exec (@parameter_name) is also valid; however, it may take more keystrokes. For example, the dynamic SQL command exec (@sproc ="7") passes the integer value 7 to the procedure, but this can also be accomplished using exec @sproc 7.
You cannot use text, unitext, and image columns as parameters to stored procedures or as values passed to parameters.
Executing a procedure specifying output for a parameter that is not defined as a return parameter in create procedure causes an error.
You cannot pass constants to stored procedures using output; the return parameter requires a variable name. You must declare the variable’s datatype and assign it a value before executing the procedure. Return parameters cannot have a datatype of text, unitext, or image.
You need not use the keyword execute if the statement is the first one in a batch. A batch is a segment of an input file terminated by the word “go” on a line by itself.
Since the execution plan for a procedure is stored the first time it is run, subsequent run time is much shorter than for the equivalent set of standalone statements.
Nesting occurs when one stored procedure calls another. The nesting level is incremented when the called procedure begins execution and is decremented when the called procedure completes execution. The nesting level is also incremented by one when a cached statement is created. Exceeding the maximum of 16 levels of nesting causes the transaction to fail. The current nesting level is stored in the @@nestlevel global variable.
Return values 0 and -1 through -14 are currently used by Adaptive Server to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use. See return for a list of values.
Parameters are not part of transactions, so if a parameter is changed in a transaction that is later rolled back, its value does not revert to its previous value. The value that is returned to the caller is always the value at the time the procedure returns.
If you use select * in a stored procedure, the procedure does not pick up any new columns you might have added to the table using alter table, even if you use the with recompile option. To do so, you must drop and re-create the stored procedure, or else an insert based on a select * can cause erroneous results. Even if the newly added column has a default bound to it, the result of the insert is NULL for the newly added column.

When you drop and re-create the stored procedure or reload the database, you see an errror message if the column defintions of the target table do not match the select * result.
Commands executed via remote procedure calls cannot be rolled back.
The with recompile option is ignored when Adaptive Server executes an extended stored procedure.

Dynamically executing Transact-SQL

When used with the string or char_variable options, execute concatenates the supplied strings and variables to execute the resulting Transact-SQL command. This form of the execute command may be used in SQL batches, procedures, and triggers.
You cannot supply string and char_variable options to execute the following commands: use, exec(string) (not the execute stored procedure), connect, begin transaction, rollback, commit, and dbcc.
The contents of the string or char_variable options cannot reference local variables declared in the SQL batch or procedure.
string and char_variable options can be concatenated to create new tables. Within the same SQL batch or procedure, however, the table created with execute is visible only to other execute commands. After the SQL batch or procedure has completed, the dynamically created table is persistent and visible to other commands.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

execute permission defaults to the owner of the procedure, who can transfer it to other users.

The permission to execute Transact-SQL commands defined with the string or char_variable options is checked against the user executing the command, unless the procedure was set up using the execution mode “dynamic ownership chain.” See sp_procxmode.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – all input parameters Proxy information – original login name, if set proxy is in effect
39	exec_trigger	Execution of a trigger	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect