Execute Stored Procedure when SQL Server starts

The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution – i.e it runs every time SQL Server service is started.

Here’s how to execute a Stored Procedure when SQL Server starts

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'true'

To disable the stored procedure again

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'false'

Note: For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the ‘master’ database and cannot contain INPUT or OUTPUT parameters.