sp_procoption (T-SQL)

Sets or shows procedure options.

Syntax

sp_procoption [[@ProcName =] 'procedure']
    [,[@OptionName =] 'option']
    [,[@OptionValue =] 'value']

Arguments

[@ProcName =] 'procedure'

Is the name of the procedure for which to set or view an option. procedure is nvarchar(776), with a default of NULL.

[@OptionName =] 'option'

Is the option to set to true or false, or on or off, or the option about which to display information. Wildcard characters cannot be used. option is varchar(35), with a default of NULL. The only value for option is startup, which sets stored procedure for autoexecution. A stored procedure that is set to autoexection runs every time Microsoft® SQL Server™ is started.

[@OptionValue =] 'value'

Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with a default of NULL.

Return Code Values

0 (success) or error number (failure)

Result Sets

Execute sp_procoption without procedure to display the list of available procedure options for all stored procedures.

Execute sp_procoption with option_name but without a value to display status for the procedure specified.

If no parameters are specified, sp_procoption returns this result set:

Available Proc Options              Change-ability

----------------------------------- --------------

startup                             change-able

Permissions

Execute permissions default to members of the sysadmin and setupadmin fixed server roles. Startup procedures must be owned by the database owner in the master database.

See Also

System Stored Procedures