SQL Server periodically executes SQL statements

First:

Formulation of scheduled jobs

Enterprise Manager

-- Manage

-- SQL Server proxy

-- Right-click a job

-- Create a job

-- Enter the job name in "general"

-- "Step"

-- New

-- Enter the step name in "Step name"

-- Select "Transact-SQL script (tsql)" in "type )"

-- "Database": select the database for Command Execution

-- Enter the statement to be executed in "command:

Exec stored procedure name... -- this stored procedure is used to create a table

-- OK

-- "Scheduling" item

-- Create Scheduling

-- Enter the scheduling name in "name"

-- Select your job execution schedule in "scheduling type"

-- If "repeated appears" is selected"

-- Click "change" to set your schedule

Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:

My computer -- control panel -- Administrative Tools -- service -- Right-click SQLServerAgent -- properties -- start type -- select "auto start" -- OK.

Note:

1. select "database maintenance" for the category in the properties of the new job. 2. during scheduling, You need to select the specific time point for daily execution to ensure that the SQL commands for the steps are executed at the specified time.

SQL Server periodically executes SQL statements

Stored Procedure

Set ansi_nulls on
Set quoted_identifier on
Go

Alter procedure [DBO]. [automodify]
As
Declare @ ID int
Declare @ order_status int
Declare @ fhtime datetime
Declare @ add_date datetime
Declare pcurr cursor
Select ID, order_status, dateadd (day, 10, fhtime) as fhtime,
Dateadd (day, 14, add_date) as add_date from DBO. orderinfo where order_status = 1 or order_status = 3
Open pcurr
Fetch next from pcurr into @ ID, @ order_status, @ fhtime, @ add_date
While (@ fetch_status = 0)
Begin
If (@ order_status = 3)
Begin
If (@ fhtime <getdate ())
Begin
-- Print 'A'
Update DBO. orderinfo set order_status = 4 where [ID] = @ ID
End
End
Else if (@ order_status = 1)
Begin
If (@ add_date <getdate ())
Begin
-- Print 'B'
Update DBO. orderinfo set order_status = 0 where [ID] = @ ID
End
End
Fetch next from pcurr into @ ID, @ order_status, @ fhtime, @ add_date
End
Close pcurr
Deallocate pcurr

 

Exec [automodify]