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]

or

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.

NoteUsing 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


Dynamically executing Transact-SQL

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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.

The permission checks for execute differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be the procedure owner or a user with execute permission on the procedure.

Granular permissions disabled

With granular permissions disabled, you must have execute permission on the procedure.

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

See also

Commands create procedure, drop procedure, return

System procedures sp_addextendedproc, sp_depends, sp_dropextendedproc, sp_helptext, sp_procxmode