Scanning Individual Records In SQL Server and VFP

At work, it's been a common thing for me to execute my own queries in our SQL Server for reasons like fixing invalid entries or just for reporting purposes. Our POS and Data Management system are working fine, but the need for database administrator's intervention like myself is far from over. There are still data that needs to be pulled out from the database which the system cannot produce quickly.

Once, I was asked to update a few records from the database of which values in fields are in dire need of updates. The task was a bit tricky as each row must be evaluated individually. I originally planned to write a short program in VFP using an
ODBC connection to connect with the Data server and make use of Pass Through (SPT) commands to execute my queries remotely. But I decided to try something else instead of my usual tactics.

So this time, I tried to mimic that of Visual Foxpro 9's SCAN/ENDSCAN command which enables us to go through each row in a table. This will make it possible to process the updates within the SQL Server Management Studio. But for security and confidentiality concern, I'll just write a pseudo-code just to show you how we can make the SQL Server behave like VFP for this topic.

The Problem

A table contains records of 3 students; the fields in that table are Unique ID, Name, Grade and Status. The aim is to fill the status with remarks based on the student's current grade. Here's the criteria:

Grade:
75 and Below = Failed
75 to 89 = Pass
90 to 100 = Excellent


VFP Scan/EndScan Approach
So basically, we have to come up with a table which contains 3 records with corresponding data. Let's begin with Visual Foxpro 9 first.

To create a table, we must execute these lines either in the command window or in a PRG file.

Create Table TmpMaster (UID Int Autoinc Not Null, xName c(60) Null, Grade Int, Status c(10) Null);

Insert Into TmpMaster (xName,Grade) ;
Values('GLEN',80)

Insert Into TmpMaster (xName,Grade) ;
Values('SOL',70)

Insert Into TmpMaster (xName,Grade) ;
Values('MERINISSA',90)

The above code should give us this table when browsed:

Notice that the values of the Status field are blank. What I had in mind is to go through each record and evaluate the values in the field Grade and write the resulting remark in the Status field.

Here's the code for that:
Select TmpMaster
Scan
If Rlock()
Replace Status With ;
Iif(Between(Grade,0,74),'FAILED',Iif(Between(Grade,75,89),'PASS','EXCELLENT'))
UnlockEndif
Endscan
Browse Normal

To explain the flow of the codes above; it scans every record starting from the top and down to the last one. The Rlock is just to lock a single row being evaluated but it can run without it. As a common practice that I've grown accustomed to, I just included it there.

The IIF() function evaluates each grades and the Replace command does the job of writing the remarks.

It's actually pretty simple with VFP and here's the resulting table after it ran.


SQL Server's Endeavor
The SQL server has a bit more complicated way of doing the same thing. Unlike the data centric and rich-in-function VFP which is straightforward, the SQL Server relies on Transact-SQL and the rules are govern by it. So we need only to make use of what it can offer and do which I believe is vast as well.

Here's the counterpart code which I wrote in SQL Server 2005.

Use Test;

Begin
Create Table TmpMaster (UID uniqueidentifier Not Null,
xName varchar(60) Null, Grade Int Null,
Status varchar(10) Null);

Insert Into TmpMaster (UID,xName,Grade)
Values(NewID(),'GLEN',80)

Insert Into TmpMaster (UID,xName,Grade)
Values(NewID(),'SOL',70)

Insert Into TmpMaster (UID,xName,Grade)
Values(NewID(),'MERINISSA',90)
End


Begin
Declare @lUID varchar(50), @lName varchar(60),
@lGrade Int, @lStatus varchar(10)

Declare TmpCursor Cursor For
Select * From TmpMaster

Open TmpCursor;

Fetch Next From TmpCursor Into @lUID, @lName, @lGrade, @lStatus

While @@Fetch_Status = 0
Begin
Begin Tran
If @lGrade Between 0 and 74

Begin
Update TmpMaster set Status = 'FAILED' where UID = @lUID

If @@error <> 0 Begin Rollback Tran Goto Finish End

End
If @lGrade Between 75 and 89

Begin
Update TmpMaster Set Status = 'PASS' where UID = @lUID

If @@error <> 0 Begin Rollback Tran Goto Finish End

End

If @lGrade Between 90 and 100

Begin
Update TmpMaster Set Status = 'EXCELLENT' where UID = @lUID

If @@error <> 0 Begin Rollback Tran Goto Finish End

End

Fetch Next From TmpCursor Into @lUID, @lName, @lGrade, @lStatus

Commit Tran
Finish:
End

Close TmpCursor;
Deallocate TmpCursor;

Select * FROM TmpMaster;
Drop Table TmpMaster;

End
Go

Let me explain the script a little bit; I created a temporary Database named Test and then created the TmpMaster table within and had it populated with similar records that we used in our example for VFP.

I then declared a few variables for the cursor that I created and named TMPCursor. The variables are there to hold the values returned by fetching. Unlike the example in VFP where we actually scanned the main table, we made a cursor on this example and fill that cursor with records from the TMPMaster via the Fetch Command.

I used the @@Fetch_Status function to check if the last FETCH statement was successful hence the "= 0" condition. So as long as the @@Fetch_status function keeps on returning zero (0), it will continue to fill the variables with records from the cursor.

The command Begin Tran was there to enable me to undo the changes if an error arises. It can run without it but as I've said earlier, there are things which I have grown accustomed to in programming which served as a fail safe for my coding.

Notice that I removed the cursor after using it with DEALLOCATE function and dropped the table as well after.

This is the table generated in SQL Server before it has been updated.

And this is the outcome after running the script.

So far, it did mimic what the VFP can do. We may have gone through a long way of coding it in SQL server but it does job. One thing I love with SQL Server is it's ability to handle data in many ways which keeps me loving it more day by day.

If you have a better way of doing this with SQL Server please feel free to comment. That's all for tonight. Have fun. ^_^