title: Stopping long running stored procedure from script
body: <p>I have a set of maintenance tasks in several databases that are triggered by a master stored procedure.  The master stored procedure is started using sp_procoption to start on mssql startup.  Then at a specified day and time, runs the child procedures in all of my databases.</p>
  
  <p>I need to update this master script.</p>
  
  <p>To clean up the update procedure, and not require stopping and restarting sql (to ensure that only one instance of the master procedure is running), I would like to be able to stop the stored procedure then restart it at the end of the update script.  Restarting is not an issue, but does anyone know a way to stop the procedure from the update script?</p>
  
  <p>Here is the script for my master scheduling proc:</p>
  
  <pre><code>USE master
  GO
  IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'weekly_maintenance_task')
      DROP PROCEDURE weekly_maintenance_task
  GO
  CREATE PROCEDURE [dbo].[weekly_maintenance_task]
  AS
  BEGIN
  
      SET NOCOUNT ON
      DECLARE @timeToRun nvarchar(50)
      SET @timeToRun = '02:30:00'
      DECLARE @dayToRun nvarchar(10)
      SET @dayToRun = 'SUNDAY'
  
  
      WHILE 1 = 1
      BEGIN
          WAITFOR time @timeToRun
          BEGIN
              DECLARE @dayOfWeek NVARCHAR(10)
              SELECT @dayOfWeek = CASE DATEPART(weekday, GETDATE())
              WHEN 1 THEN 'SUNDAY'
              WHEN 2 THEN 'MONDAY'
              WHEN 3 THEN 'TUESDAY'
              WHEN 4 THEN 'WEDNESDAY'
              WHEN 5 THEN 'THURSDAY'
              WHEN 6 THEN 'FRIDAY'
              WHEN 7 THEN 'SATURDAY'
              END
              IF (@dayOfWeek = @dayToRun)
              BEGIN
                  EXECUTE sp_msforeachdb 'USE ?
                  IF DB_NAME() NOT IN (''master'', ''msdb'',''tempdb'',''model'')
                      IF EXISTS (select * from sys.procedures where name=''database_maintenance_weekly'')
                          EXECUTE database_maintenance_weekly'
              END
          END
      END
  END
  
  GO
  
  sp_procoption @ProcName = 'weekly_maintenance_task',
                @OptionName = 'startup',
                @OptionValue = 'on'
  GO
  </code></pre>