SQL Cursor

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

The basic syntax of a cursor is:

Declare @vStyleID varchar(50)
Declare @Qty numeric

set @Qty = 0

DECLARE CurClosing CURSOR FOR
select DISTINCT vStyleID from tmprptstylewiseclosingreport
OPEN CurClosing

FETCH NEXT FROM CurClosing
INTO @vStyleID

WHILE @@FETCH_STATUS = 0
BEGIN
set @Qty = (select sum(iTotalQty) from vwAutogenID where vStyleID = @vStyleID)

update tmprptstylewiseclosingreport
set nOrderQty = @Qty
where vStyleID = @vStyleID

set @Qty = 0

FETCH NEXT FROM CurClosing
INTO @vStyleID
end

CLOSE CurClosing
DEALLOCATE CurClosing

Based on the example above, cursors include these components:
• DECLARE statements – Declare variables used in the code block
• SET\SELECT statements – Initialize the variables to a specific value
• DECLARE CURSOR statement – Populate the cursor with values that will be evaluated
NOTE – There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement – Open the cursor to begin data processing
• FETCH NEXT statements – Assign the specific values from the cursor to the variables
NOTE – This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement – Condition to begin and continue data processing
• BEGIN…END statement – Start and end of the code block
NOTE – Based on the data processing multiple BEGIN…END statements can be used
• Data processing – In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement – Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement – Destroys the cursor

MS SQL Server Job

A SQL Server job is a collection of steps executed by the database engine by SQL Server Agent.

The job can perform many different functions within it that can save time and effort on the part of employees.

Let us consider a procedure PrcDailyUpdateForecast which will be executing hourly.

Minimally, it takes two or three steps to set up a scheduled task to execute the code of your choice. First, go to the Management node, SQL Server Agent, and highlight jobs. Right-click, new job, and give it a name like UpdateForecast. Move to the steps tab, add a step called S1.

The first tab (General) of this dialog shows the name of the job, when the job was created, whether or not the job is enabled, the category the job falls into, who owns the job within the database and a description of the job.

To disable a SQL Server job, uncheck the Enabled checkbox and the job will no longer run in the database if it was scheduled to do so.

The Steps tab of this dialog brings up the following screen:

Each step within the job will be identified within this screen.

These steps can include executing data transformation packages, executing queries, executing stored procedures, etc.

Within this interface you can move the order of the steps around so that they occur in a specific order, as well as, add new steps or edit existing steps.

If you select a step and select the Edit button, the following screen appears:

This screen is used to set the command that is to be executed during the current step.

You can also specify the name of the step, the type of command to execute and the exit code of the process if successful.

The tab also has a Schedule tab that can be used to manage the schedule of the SQL Server jobs.

This will allow the job to be set to occur once a daily, multiple times per day, weekly, monthly or just a one time. The job can be set to run at a certain times per day on a recurring basis as well, such as every hour or at a certain time per day.

The following screen shows the scheduling tab:

To work with the schedule, click the New Schedule or the Edit button.

The New Schedule button will allow a new schedule to be input for the job.

The Edit button will allow the edit of the current schedule.

There is also a Notifications tab that can be used to handle notifications from the SQL Server in the event that something unusual occurs with the job while it is running.

The following screen shows the notification dialog which allows the selection of notifications options:

Notifications can be sent in multiple methods. For the notification to work, there must be operators set up within SQL Server to receive them.

The notification can be an email, a page or a net send. Each notification can be used to alert the operator that something has occurred on the server and the actions are controlled by SQL Server Agent.

Once the SQL Server job is configured, the changes can be saved and the job can now be used to perform routine tasks on a regular basis which can free up resources within your organization.