Create Stored Procedures That Run At SQL Server Startup

Create Stored Procedures That Run At SQL Server Startup

Let's say you have a table and you want to make sure that you clear it every time SQL Server is restarted What would be the easiest way to accomplish that? Well you can create a procedure and have it execute every time the SQL Server is restarted The procedure has to be created in the master database, after it is created you have to use sp_procoption to have the procedure execute when SQL Server starts up.

--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO


--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'

--This will return the proc name since we enabled the ExecIsStartup property
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1

--disable the execution of the proc on start up
sp_procoption prStartUp,startup,'off'

--Let's check again, no rows should be returned now
SELECT name FROM sysobjects
WHERE xtype = 'p'
AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1