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 = ON) ON [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.