Schedule Task in Sql Server

Schedule Task in Sql Server
In Sql Server, there is an interesting concept called Schedule. With this we can do some automatic functionality.
For example, I want to check goods stock daily. For this I can create a job to check the stock and schedule that job to be executing in time basis.

Here, I have taken the objects from pubs database.
I have created one new table named ‘hsp_CheckStock’.
Actually what I did is,
Checking the stores table whether the Quantity is > 10. If it is then I will put the corresponding title and qty into hsp_checkstock table with date of updated. This is done in stored procedure named ‘sp_hsp_UpdateStock’.

use pubs
create table hsp_CheckStock (title varchar(100), qty int, updatedon datetime)

create procedure sp_hsp_UpdateStock
as
insert hsp_checkstock
select t.title, s.qty, getdate() as updatedon from sales s, titles t
where s.title_id = t.title_id and s.qty > 10;

use msdb
/* Create Job --- For calling from schedule. Click here for syntax */
exec sp_add_job
@job_name = 'job_hsp_updatestock'

/* Create Job step --- For Executing the stored procedure. Click here for syntax */
exec sp_add_jobstep
@job_name = 'job_hsp_updatestock',
@step_name = 'jobstep_hsp_updatestock',
@subsystem = 'TSQL',
@command = 'exec pubs.dbo.sp_hsp_UpdateStock'

/*Click here for syntax */
exec sp_add_jobserver
@job_name = 'job_hsp_updatestock'

/* Create Schedule --- For executing the job at specific time base. Click here for syntax */

exec sp_add_jobschedule
@job_name = 'job_hsp_updatestock',
@name = 'jobschedule_hsp_updatestock',
@freq_type = 4, --- Daily
@freq_interval = 1,
@active_start_time = 160000 --- 4PM

Now, the checking process is doing in automatic manner. The same concept you can try for automatic email reminder.