Sp_procoption in sql server

SQL Server offers the system stored procedure sp_procoption which can be used to designate one or more stored procedures to automatically execute when the SQL Server service is started.

exec sp_procoption @ProcName = ['stored procedure name'], @OptionName = 'STARTUP', @OptionValue = [on|off]

Parameter @ProcName is self-explanatory; it's the name of the procedure marked for auto-execution

Parameter @OptionName is the option to use. The only valid option is STARTUP

Parameter @OptionValue toggles the auto-execution on and off

Using sp_procoption comes with certain restrictions:

Ø  We must be logged in as a sysadmin to use sp_procoption

Ø  We can only designate standard stored procedures, extended stored procedures, or CLR stored procedures for startup

Ø  The stored procedure must be located in the master database

Ø  The stored procedure must not require any input parameters or return any output parameters