Configure stored procedure for auto execution in SQL

Microsoft provided a system Procedure “sp_procoption”, this procedure helps to configure a stored procedure for automatic execution when the SQL Server will start. Start up procedure must be existed in master database of SQL Server and this procedure doesn’t contain any parameter, if it contains any parameter then it won’t run. Also this procedure should be created by using sysadmin or dbo privileged user.

This procedure start executes, whenever SQL Server services starts.

Syntax:

sp_procoption 'Proc_Name','Option','value'

Proc_Name : Name of the Procedure

Option: currently one option is available, startup

Value: Either {True or false} OR {On, Off}

Sample Script to configure SP for Auto-Execution:

USE master

EXEC sp_procoption 'Calc_Today_Opening_Balance', 'startup', 'true')

If the procedure runs successfully, it will returns 0, else it will error number then continue the SQL server services internal works.