Runs a procedure or dynamically executes Transact-SQL commands.
[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]...)
is used to execute a stored procedure or an extended stored procedure (ESP). This parameter is necessary only if the stored procedure call is not the first statement in a batch.
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.
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.
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.
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.
is the name of a procedure defined with create procedure.
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
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.
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.
is the name of a variable used to store a return parameter.
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.
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 (ESP).
Using execute procedure with
recompile many times can adversely affect the procedure
cache performance. Since a new plan is generated every time you use with
recompile, a useful performance plan may age out of the
cache if there is insufficient space for new plans.
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.
is the name of a variable that supplies the text of a Transact-SQL command.
All three examples 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
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"
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
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
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
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')
Executes sp_who:
declare @sproc varchar(255) select @sproc = "sp_who" execute @sproc
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 do not have to 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 be accomplished with fewer keystrokes as exec @sproc 7.
You cannot use text 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 or image.
It is not necessary to 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 it is decremented when the called procedure completes execution. 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 create procedure, the procedure does not pick up any new columns you may have added to the table (even if you use the with recompile option to execute). You must drop the procedure and re-create it.
Commands executed via remote procedure calls cannot be rolled back.
The with recompile option is ignored when Adaptive Server executes an extended stored procedure.
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: begin transaction, commit, connect to, declare cursor, rollback, dump transaction, dbcc, set, use, or nested execute commands.
The create view command can be specified using execute(), but only in SQL batches. create view cannot be used in procedures, either as a static command or as a string parameter to execute().
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.
ANSI SQL – Compliance level: Transact-SQL extension.
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. This is true even when execute() is defined within a procedure or trigger that belongs to another user.
Commands create procedure, drop procedure, return
System procedures sp_addextendedproc, sp_depends, sp_dropextendedproc, sp_helptext
