SQL Server scheduled stored procedure

In this SQL Server tutorial, we are going to talk about SQL Server scheduled stored procedure. Here we will learn about different methods of scheduling a stored procedure in SQL Server. And we will also understand the following topics.

• SQL Server scheduled stored procedure
• Scheduled run of stored procedure in SQL Server
• SQL Server schedule stored procedure without agent
• SQL Server schedule stored procedure job
• SQL Server automated stored procedure
• SQL Server express scheduled stored procedure
• SQL Server agent job schedule stored procedure
• SQL Server schedule stored procedure to run

SQL Server scheduled stored procedure

In this section, we will try to understand how to schedule a stored procedure in SQL Server. So, we will first create a stored procedure in SQL Server and then understand how to schedule its execution. And with this, the stored procedure will be executed execute automatically at the given time.

SQL Server agent job schedule stored procedure

Now, for this implementation, we will use the SQL Server Agent service. A SQL Server Agent is a service that is used to execute administrative tasks. Also, it allows to schedule execution of these administrative tasks also known as Jobs in SQL Server.

Note: The SQL Server Agent is not available in the Express edition, we use some other edition.

Moreover, the information related to a job is stored in SQL Server and the job consists of one or more steps. Now, each job step consists of its own task, for example, executing a stored procedure.

So, in this section, we will learn how to use the SQL Server Agent to schedule a job of executing a stored procedure in SQL Server.

Note: Remember, by default the SQL Server Agent service is disabled. So, to use this service first, we need to start it. And if you face any issues then, you can refer to SQL Server Agent won’t start.

In SQL Server, we can schedule a job either by using the SQL Server Management Studio or we can use Transact-SQL. And we will discuss both methods using an example. Now, for the example, we have created a sample table that will date and time values. The query for the table is given below.

USE [sqlserverguides]
GO

CREATE TABLE Sample_data (
	id INT IDENTITY(1,1) NOT NULL,
	entry_date DATE,
	entry_time TIME
);

Next, we will create a stored procedure that will insert the date and time values into the table. The script for the stored procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_InsertDateTime
AS
BEGIN
  DECLARE @date_value DATE,
          @time_value TIME
  
  SET @date_value = CAST(GETDATE() AS DATE)
  SET @time_value = CAST(GETDATE() AS TIME)
  
  INSERT INTO dbo.Sample_data ( entry_date, entry_time ) 
  VALUES(@date_value, @time_value)
END

In the above code, we have created a stored procedure with the name “usp_InsertDateTime“. And this procedure will insert the date and time value into the table whenever it is executed.

Now, we will learn how to schedule this procedure using different methods in SQL Server.

Read How to use union in view SQL Server

Using SQL Server Management Studio

Now, let’s understand how we can schedule a stored procedure in SQL Server using SQL Server Management Studio. And for this task, we have to follow the given steps.

• In the Management Studio, correct the database instance and then, expand the instance.
• Next, expand the SQL Server Agent and right-click on the Jobs directory, and click on “New Job“.

• After this, a new Job window will be opened. And in that, first, we need to specify the job name, owner, category, and description.

• Now, open the next Steps page and click on the New option. And then, provide the Step Name, Type, Database, and procedure execution command.

• Next, open the Schedule page and again click on the “New” option and then, specify the given options to schedule the execution. In the given example, we are scheduling the procedure after every 5 minutes.

• In the end, click on the OK option to create a new job. After this, again right-click the Jobs directory and click on the “Start Job at Step” option.

• And with all these steps, we have created an Agent job that will execute the stored after every 5 minutes.

Now, we confirm the execution of a stored procedure by querying the table.

Using Transact-SQL

Now, let’s understand how we can schedule the execution of a stored procedure using a Transact-SQL script. For this implementation, consider the following script.

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'Insert Table Data Proc' ;  --Job Name
GO  
EXEC sp_add_jobstep  
    @job_name = N'Insert Table Data Proc',  
    @step_name = N'Run Procedure',           --Step Name
    @subsystem = N'TSQL',                    --Step Type 
    @command = N'EXEC usp_InsertDateTime'    --Command to be executed
GO  
EXEC dbo.sp_add_schedule  
    @schedule_name = N'RunProc',             --Schedule Name
    @freq_type = 1,                          --Only one day execution  
	@freq_subday_type = 0x4,                 --Execution interval in minutes  
	@freq_subday_interval = 5;               --Execution interval 
USE msdb ;  
GO  
EXEC sp_attach_schedule  
   @job_name = N'Insert Table Data Proc',  
   @schedule_name = N'RunProc';  
GO  
EXEC dbo.sp_add_jobserver  
    @job_name = N'Insert Table Data Proc';  
GO  

In the above script, first, we have created a job with a name and then, we have added the job steps. After adding the steps, we have added a schedule for the job. In the end, we have added the job to the server for execution.

Also, this schedule is similar to what we have done while using SQL Server Management Studio.

Also, check: SQL Server find text in stored procedure

SQL Server schedule stored procedure without agent

In this section, we will understand how to schedule a stored procedure in SQL Server without using the SQL Server Agent.

Now, scheduling the execution of a stored procedure can be done in multiple ways. And we will try to illustrate all the methods with examples.

Method-1

For the understanding of this method, we will illustrate a simple example. And in the example, first, we will create a simple table. This table will only have 2 columns first is the id and the second is a DateTime column.

USE [sqlserverguides]
GO

CREATE TABLE SimpleTable (
	id INT IDENTITY(1,1) NOT NULL,
	entry_datetime DATETIME
);

Next, we will create a stored procedure that will insert data into this table after every 3 minutes. And the script for the stored procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROC ScheduledProc
AS 
BEGIN
    INSERT INTO dbo.SimpleTable ( entry_datetime ) 
    VALUES(GETDATE());
END

BEGIN
    WAITFOR DELAY '00:02:00';
    EXEC ScheduledProc
END

In the above code, we have created a stored procedure that will be executed automatically after every 2 minutes. And the procedure will insert the current system DateTime value into the table.

Now, if we execute the stored procedure once and then, query the table after some time then, we will get the following result.

Method-2

In this method, we will use the Task Scheduler in Windows to run the SQLCMD utility. And using that utility execute the stored procedure at the given schedule.

For this implementation, we are going to use the same table as shown in the previous section.

USE [sqlserverguides]
GO

CREATE TABLE SimpleTable (
	id INT IDENTITY(1,1) NOT NULL,
	entry_datetime DATETIME
);

And now, we will create a stored procedure that will insert the current Datetime value into the table.

USE [sqlserverguides]
GO

CREATE PROC ScheduledProc
AS 
BEGIN
    INSERT INTO dbo.SimpleTable ( entry_datetime ) 
    VALUES(GETDATE());
END

Now, we will use the Task scheduler in Windows to schedule the execution of this stored procedure. And for this task, we will use the following steps.

• First, run the Task Scheduler in Windows and click on “Create Basic Task“.
• After this, a new window will appear where we need to specify the task name and description.

• Next, we need to select the trigger option from the given list and then, click “Next“. In our case, we are selecting the Daily option.

• On the next page, specify the date and time on which we want to schedule the stored procedure.

• Next, we have to select the action for our task. And for executing a stored procedure, we will select the “Start a program” option.
• After this, we need to create a .bat file with the following script in it.

sqlcmd -S DESKTOP-EFDCLUP -E -Q "USE [sqlserverguides] EXEC [dbo].[ScheduledProc]" 

By using the given script, we are executing the stored procedure [dbo].[ScheduledProc].

• Now, on the next Task scheduler page, we have to specify the path of the above script. And in the end, click on the Finish button to create the task.

Now, the script for executing the stored procedure will be automatically called at the selected time.

Read: Full-text search in SQL Server

SQL Server schedule stored procedure job

In SQL Server, we generally use the SQL Server Agent to schedule any task including the execution of a stored procedure. Now, to schedule any task, we have to create Job using SQL Server Agent.

A job can be specified as a set of actions that the SQL Server Agent needs to perform. And we generally define jobs for some administrative tasks that need to be executed multiple times. Moreover, a job can be run on a single local server or several remote servers.

So, to schedule the execution of a stored procedure in SQL Server, we have to first create a job using the SQL Server Agent. And then, schedule that schedule according to our requirements.

The complete detail on how to schedule a stored procedure job is already explained in this section.

Read: SQL Server check user permissions on table

SQL Server express scheduled stored procedure

In SQL Server, the best way to schedule a stored procedure is by using the SQL Server Agent. But, the SQL Server Agent is not available in the SQL Server Express Edition. So, by using SQL Server Express, we cannot use the Agent service to schedule the execution of a stored procedure.

Still, there are few methods to schedule the execution of a stored procedure. For example, we can use the Windows Task Scheduler to schedule the execution of a script that consists of the execution of a procedure.

For more detail, you can refer to the SQL Server schedule stored procedure without an agent.

SQL Server automated stored procedure

An automated stored procedure in SQL Server is the one that gets executed automatically. Now, to automate the execution of a stored procedure, we need to schedule its execution. So, when the specified time has reached the stored procedure will automatically get executed.

Now, in SQL Server, we usually use the SQL Server Agent to schedule a job that consists of executing a stored procedure. But, if you are using the SQL Server Express then, you might not able to use the SQL Server Agent.

Now, we have already explained how to schedule a stored procedure with and without using the agent. For more details, you can refer to the first topic in this article.

You may also like to read the following SQL Server tutorials.

• How to get list of users in SQL Server
• How to delete a view in SQL Server
• Drop stored procedure SQL Server
• How to test stored procedure in SQL Server
• How to check if SQL Server is running
• Loop in SQL Server stored procedure
• Try catch in SQL Server stored procedure
• How to select latest record in SQL Server

So, in this tutorial, we have learned about SQL Server scheduled stored procedure. And we have also discussed the following topics.

• SQL Server scheduled stored procedure
• Scheduled run of stored procedure in SQL Server
• SQL Server schedule stored procedure without agent
• SQL Server schedule stored procedure job
• SQL Server automated stored procedure
• SQL Server express scheduled stored procedure
• SQL Server agent job schedule stored procedure
• SQL Server schedule stored procedure to run