Kenneth Fisher describes startup stored procedures:
sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.
There are a few uses cases in which startup stored procedures can be useful, but my reservation about them is similar to my reservation about triggers: it’s not apparent to people that a startup stored procedure is in place, so if there is a problem with it, troubleshooting might be harder than normal without good documentation.