sp_procxmode

Displays or changes the execution modes associated with stored procedures.

Syntax

sp_procxmode [procname [, tranmode]]

Parameters

procname – is the name of the stored procedure with the transaction mode you are examining or changing.
tranmode – is the new execution mode for the stored procedure. Values are "chained", "unchained", and "anymode", for transaction modes, and '[No] Dynamic Ownership Chain'.

Examples

Example 1 – Displays the transaction mode for all stored procedures in the current database:

sp_procxmode

procedure name      user name   transaction mode
------------------  ---------   ----------------
byroyalty           dbo         Unchained
discount_proc       dbo         Unchained
history_proc        dbo         Unchained
insert_sales_proc   dbo         Unchained
insert_detail_proc  dbo         Unchained
storeid_proc        dbo         Unchained
storename_proc      dbo         Unchained
title_proc          dbo         Unchained
titleid_proc        dbo         Unchained

Example 2 – Displays the transaction mode of the stored procedure byroyalty:

sp_procxmode byroyalty

procedure name                   transaction mode 
------------------------------   ---------------- 
byroyalty                        Unchained

Example 3 – Changes the transaction mode for the stored procedure byroyalty in the pubs2 database from “unchained” to “chained”:
```
sp_procxmode byroyalty, "chained"
```

Usage

There are additional considerations when using sp_procxmode:

To change the transaction mode of a stored procedure, you must be the owner of the stored procedure, the owner of the database containing the stored procedure, or the system administrator. The database owner or system administrator can change the mode of another user’s stored procedure by qualifying it with the database and user name. For example:
```
sp_procxmode "otherdb.otheruser.newproc", "chained"
```
To use sp_procxmode, turn off chained transaction mode using the chained option of the set command. By default, this option is turned off.
When you use sp_procxmode with no parameters, it reports the transaction modes of every stored procedure in the current database.
To examine a stored procedure’s transaction mode (without changing it), enter:
```
sp_procxmode procname
```
To change a stored procedure’s transaction mode, enter:
```
sp_procxmode procname, tranmode
```
When you create a stored procedure, the SAP ASE server tags it with the current session’s transaction mode. This means:
- You can execute “chained” stored procedures only in sessions using chained transaction mode.
- You can execute “unchained” stored procedures only in sessions using unchained transaction mode.
To execute a particular stored procedure in either chained or unchained sessions, set its transaction mode to “anymode”.
If you attempt to run a stored procedure under the wrong transaction mode, the SAP ASE server returns a warning message, but the current transaction, if any, is not affected.
Executing sp_procxmode procname, 'Dynamic Ownership Chain' makes sure that any Dynamic SQL (execute immediate) statements within the stored procedure get their permissions checked against the procedure creator.
Executing sp_procxmode procname, 'No Dynamic Ownership Chain' (the default behaviour if omitted) makes sure that any Dynamic SQL (execute immediate) statements within the stored procedure get their permissions checked against the procedure executor.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be the owner of the procedure or a user with `manage database` privilege. Any user can execute sp_procxmode to for its own procedure.
Disabled	With granular permissions disabled, you must be the database owner, the owner of the procedure, or a user with sa_role. Any user can execute sp_procxmode to display the transaction mode.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
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 in effect