SQL Server
United States (English) Sign in
Home 2012 2008 R2 2008 Previous Versions Library Forums
TechNet Library
SQL Server
SQL Server 2012
Product Documentation
Books Online for SQL Server 2012
Database Engine
Transact-SQL Reference (Database Engine)
System Stored Procedures (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
sp_add_data_file_recover_suspect_db (Transact-SQL)
sp_addextendedproc (Transact-SQL)
sp_addextendedproperty (Transact-SQL)
sp_add_log_file_recover_suspect_db (Transact-SQL)
sp_addmessage (Transact-SQL)
sp_addtype (Transact-SQL)
sp_addumpdevice (Transact-SQL)
sp_altermessage (Transact-SQL)
sp_attach_db (Transact-SQL)
sp_attach_single_file_db (Transact-SQL)
sp_autostats (Transact-SQL)
sp_batch_params (Transact-SQL)
sp_bindefault (Transact-SQL)
sp_bindrule (Transact-SQL)
sp_bindsession (Transact-SQL)
sp_certify_removable (Transact-SQL)
sp_clean_db_free_space (Transact-SQL)
sp_clean_db_file_free_space (Transact-SQL)
sp_configure (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_create_removable (Transact-SQL)
sp_createstats (Transact-SQL)
sp_datatype_info (Transact-SQL)
sp_db_increased_partitions
sp_db_vardecimal_storage_format (Transact-SQL)
sp_dbcmptlevel (Transact-SQL)
sp_dbmmonitoraddmonitoring (Transact-SQL)
sp_dbmmonitorchangealert (Transact-SQL)
sp_dbmmonitorchangemonitoring (Transact-SQL)
sp_dbmmonitordropalert (Transact-SQL)
sp_dbmmonitordropmonitoring (Transact-SQL)
sp_dbmmonitorhelpalert (Transact-SQL)
sp_dbmmonitorhelpmonitoring (Transact-SQL)
sp_dbmmonitorresults (Transact-SQL)
sp_dbmmonitorupdate (Transact-SQL)
sp_dbremove (Transact-SQL)
sp_delete_backuphistory (Transact-SQL)
sp_delete_database_backuphistory (Transact-SQL)
sp_depends (Transact-SQL)
sp_describe_first_result_set (Transact-SQL)
sp_describe_undeclared_parameters (Transact-SQL)
sp_detach_db (Transact-SQL)
sp_dropdevice (Transact-SQL)
sp_dropextendedproc (Transact-SQL)
sp_dropextendedproperty (Transact-SQL)
sp_dropmessage (Transact-SQL)
sp_droptype (Transact-SQL)
sp_estimate_data_compression_savings (Transact-SQL)
sp_estimated_rowsize_reduction_for_vardecimal (Transact-SQL)
sp_executesql (Transact-SQL)
sp_getbindtoken (Transact-SQL)
sp_getapplock (Transact-SQL)
sp_get_query_template (Transact-SQL)
sp_help (Transact-SQL)
sp_helpconstraint (Transact-SQL)
sp_helpdb (Transact-SQL)
sp_helpdevice (Transact-SQL)
sp_helpextendedproc (Transact-SQL)
sp_helpfile (Transact-SQL)
sp_helpfilegroup (Transact-SQL)
sp_helpindex (Transact-SQL)
sp_helplanguage (Transact-SQL)
sp_helpserver (Transact-SQL)
sp_helpsort (Transact-SQL)
sp_helpstats (Transact-SQL)
sp_helptext (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_indexoption (Transact-SQL)
sp_invalidate_textptr (Transact-SQL)
sp_lock (Transact-SQL)
sp_monitor (Transact-SQL)
sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL)
sp_refreshsqlmodule (Transact-SQL)
sp_refreshview (Transact-SQL)
sp_releaseapplock (Transact-SQL)
sp_rename (Transact-SQL)
sp_renamedb (Transact-SQL)
sp_resetstatus (Transact-SQL)
sp_sequence_get_range (Transact-SQL)
sp_server_diagnostics (Transact-SQL)
sp_setnetname (Transact-SQL)
sp_settriggerorder (Transact-SQL)
sp_spaceused (Transact-SQL)
sp_tableoption (Transact-SQL)
sp_unbindefault (Transact-SQL)
sp_unbindrule (Transact-SQL)
sp_updateextendedproperty (Transact-SQL)
sp_updatestats (Transact-SQL)
sp_validname (Transact-SQL)
sp_who (Transact-SQL)
Expand Minimize
1 out of 1 rated this helpful - Rate this topic

sp_procoption (Transact-SQL)

SQL Server 2012

Sets or clears a stored procedure for automatic execution. A stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.

Topic link icon Transact-SQL Syntax Conventions

Copy 

          

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

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

[ @OptionName = ] 'option'

Is the name of the option to set. The only value for option is startup.

[ @OptionValue = ] 'value'

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

0 (success) or error number (failure)

Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup.

Requires membership in the sysadmin fixed server role.

The following example sets a procedure for automatic execution.

Copy 
EXEC sp_procoption @ProcName = '<procedure name>' 
    , @OptionName = ] 'startup' 
    , @OptionValue = 'on';

The following example stops a procedure from executing automatically.

Copy 
EXEC sp_procoption @ProcName = '<procedure name>' 
    , @OptionValue = 'off';
Did you find this helpful?
(1500 characters remaining)

Community Additions

ADD
© 2013 Microsoft
|
Site Feedback
© 2013 Microsoft. All rights reserved.