Execute Stored Proc Using SQL Job

In this article, you will learn how to execute stored procedure periodically using SQL Server Job.

First, let’s create a sample database table, this table should have only one column date. So the scenario is, let’s insert current datetime in table using stored procedure in every one-minute duration from SQL Job.
 
Here is table script,
  1. USE [AdventureWorks2017]  
  2. GO  
  3. /****** Object:  Table [dbo].[SampleDateTime]    Script Date: 10/1/2019 9:08:22 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[SampleDateTime](  
  9.     [id] [int] IDENTITY(1,1) NOT NULL,  
  10.     [CurrentDateTime] [datetime] NULL,  
  11.  CONSTRAINT [PK_SampleDateTime] PRIMARY KEY CLUSTERED   
  12. (  
  13.     [id] ASC  
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  15. ON [PRIMARY]  
  16. GO  
  17. Store Proc:  
  18. SET ANSI_NULLS ON  
  19. GO  
  20. SET QUOTED_IDENTIFIER ON  
  21. GO  
  22. CREATE PROCEDURE PROC_InsertDateTime  
  23. AS  
  24. BEGIN  
  25.     -- SET NOCOUNT ON added to prevent extra result sets from  
  26.     -- interfering with INSERT statements.  
  27.     SET NOCOUNT ON;  
  28.     INSERT INTO dbo.SampleDateTime (CurrentDateTime) VALUES (GETDATE())  
  29. END  
  30. GO  
Let’s set up the SQL job now to run this process and schedule it for every 1 minute.
 

Why SQL Job?

 
A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks.
 

Create a SQL Job

 
Step 1
 
Expand the SQL Server Agent and right click on Jobs and click on New Job…
 
Execute Stored Proc Using SQL Job
 
In General tab, Enter job name, owner, category and description.
 
Execute Stored Proc Using SQL Job
 
In Steps tab, click New and enter step name, select Type as Transact-SQL script (T-SQL) and select database and put EXEC procedure name in command area.
 
Execute Stored Proc Using SQL Job
 
From schedules tab, click new button and put schedule name, frequency, daily frequency and duration.
 
In my job, I have scheduled it for every 1 minute.
 
Execute Stored Proc Using SQL Job
 
Now we are done here with job part, let’s start the job. Right click on job and hit Start Job at Step…
 
Execute Stored Proc Using SQL Job
 
Execute Stored Proc Using SQL Job
 
As you can see job has been successfully run, now let’s check in database table.
 
Our expectation is one record will insert in table on every minute.
 
Execute Stored Proc Using SQL Job
 
Here you go, as you can see one datetime entry in inserting in table.
 

Conclusion

 
In this article, we have learned how to create a SQL Job and schedule it and run stored procedure.