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]


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




  • 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
     (1 row affected, return status = 0)
    When executed from sybsystemprocs, it returns the value “sybsystemprocs”:
    exec sybsystemprocs..sp_foo
     (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.

    If you execute a stored procedure and specify more parameters than the number of parameters expected by the procedure, the server ignores the extra parameters.

    Note: If you spell any parameter name incorrectly, the server ignores it by treating it as an extra. You see no warnnings about the spelling error, and if the parameter is defined with a default value, the server executes the procedure using that default value instead of the value you provided with the misspelled parameter.
  • 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 the SAP ASE 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 the SAP ASE server executes an extended stored procedure.

See also sp_addextendedproc, sp_depends, sp_dropextendedproc, sp_helptext, sp_procxmode in Reference Manual: Procedures.


ANSI SQL – Compliance level: Transact-SQL extension.


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.


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


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


Values in event and extrainfo columns of sysaudits are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • 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



Audit option


Command or access audited

Execution of a trigger

Information in extrainfo
  • 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

Related reference
create procedure
drop procedure