Set Transaction Modes for Stored Procedures

Use sp_procxmode to display or change the transaction mode of stored procedures.

For example, to change the transaction mode for the stored procedure byroyalty to chained, enter:
sp_procxmode byroyalty, "chained"

sp_procxmode “anymode” lets stored procedures run under either chained or unchained transaction mode. For example:

sp_procxmode byroyalty, "anymode"

Use sp_procxmode without any parameter values to display the transaction modes for all stored procedures in the current database:

sp_procxmode
procedure name               transaction mode
-------------------------    --------------------
byroyalty                    Any Mode 
discount_proc                Unchained 
history_proc                 Unchained 
insert_sales_proc            Unchained 
insert_salesdetail_proc      Unchained 
storeid_proc                 Unchained 
storename_proc               Unchained 
title_proc                   Unchained 
titleid_proc                  Unchained 

You can use sp_procxmode only in unchained transaction mode.

To change a procedure’s transaction mode, you must be a system administrator, the database owner, or the owner of the procedure.