Configure Stored Procedures to Automatically Run at SQL Server Startup

There is a system stored procedure in SQL Server called sp_procoption that can be used to set one or more stored procedures to automatically execute at SQL Server service startup.

To run the sp_procoption system stored procedure you must be a member of sysadmin fixed server role. Stored procedure you want to run automatically at startup must be in the master database and cannot contain INPUT or OUTPUT parameters.

To set the stored procedure called 'my_stored_procedure' (that was previously created in the master database) to run at SQL Server startup, run the following code:

USE master
GO
EXEC sp_procoption N'my_stored_procedure', 'startup', 'on'
GO

To turn off the automatic execution of the stored procedure called 'my_stored_procedure' at SQL Server startup, run the following code:

USE master
GO
EXEC sp_procoption N'my_stored_procedure', 'startup', 'off'
GO

You can set up multiple procedures to automatically execute at startup. If you don't want to execute them in parallel, you can set one procedure to execute at startup and have that procedure call the other procedures.

To see how to List all Stored Procedures set to Automatically Run at SQL Server Startup, read related blog post:
List all Stored Procedures set to Automatically Run at SQL Server Startup