Erhard Smit

There’s really only one feature I that I miss from MySQL when developing web applications with a Microsoft SQL backend, and that is the limit function that restricts the index and number of records returned with a SQL Query.

I suppose there are a few options to look at here; I know a few developers suggest using a nested SELECT and the TOP keyword, then ordering the records in a descending way from a second select and using a TOP from there as well. Basically selecting the records you need by in reversing the result set and using TOP to pull the 10 or so out that you’re interested in.

There are also a few others but the one above seems to be the common trend. After lying awake for about 3 hours last night I found a much simpler solution and thought I’d share it with the world. This may not be sufficient on very large record sets but works well in my environment.

To demonstrate I’ll use only a single table called OrgMember with:

OrgMemberID INT PK

FirstName VARCHAR(50)

LastName VARCHAR(50)

To make things a little simpler I created a stored procedure for this particular table. You’ll have to create one like this for each of the tables that you want paging on or, improve this to cater for any table you want.

CREATE PROCEDURE SelectOrgMemberWithPaging

      – Add the parameters for the stored procedure here  

      @Where VARCHAR(500),

      @OrderBy VARCHAR(500),

      @FromRecord INT,

      @Limit INT 

Nothing special to the declaration of the stored proc. I am making provision for the passing the WHERE clause on the query I select as well as an order by. This is if I don’t want to select and page through all the records but only, the Smiths for example. Then follows the rest of my stored proc:

AS

BEGIN

      – SET NOCOUNT ON added to prevent extra result sets from

      – interfering with SELECT statements.

      SET NOCOUNT ON;

      DECLARE @SQL VARCHAR(2000)

      SET @SQL = ‘DECLARE @TempOrgMember TABLE (     

                        RecordIndex INT identity,

                        OrgMemberID INT,

                        Email VARCHAR(50),

                        FirstName VARCHAR(50),

                        LastName VARCHAR(50)

                        );

                        INSERT INTO @TempOrgMember SELECT OrgMemberID,Email,FirstName,LastName FROM OrgMember ‘ + @Where + ‘ ‘ + @OrderBy + ‘

                        SELECT * FROM @TempOrgMember WHERE RecordIndex >= ‘ + CAST(@FromRecord AS VARCHAR) + ‘ AND RecordIndex <= ‘ + CAST((@FromRecord + @Limit - 1) AS VARCHAR)

      EXEC (@SQL)

END

GO

And that’s it. Let me explain:

I create a temporary in-memory table to store the results of my SQL query, but I also include a first identity field for the records. This is the key of my paging stored proc. Each record will get a unique sequential index as they are inserted into the table from the SELECT I run.

Then, I insert the records into my temp table by doing a normal select from the source table with the WHERE and ORDERBY clauses attached.

Finally, I select from the temp table where RecordIndex (the unique sequential key) for each record is bigger than or equal to the FromRecord and smaller or equal to the FromRecord + LimitBy indicator.

Makes sense doesn’t it? I didn’t test for efficiency but in this project with the records we’ll be processing it should work just fine.

EXEC SelectOrgMemberWithPaging ”,”,200,10

ProEAM is the name for our online Asset Management & Preventive Maintenance System.  Otherwise known as a CMMS (Computerized Maintenance Management System).

The system was built to help companies schedule and track services on equipment, vehicles and buildings. What sets ProEAM apart from the rivals is the fact that it’s a totally web based system with a rich graphical user interface, dashboards and comprehensive reporting.

I added some initial screenshots of the product, a full demonstration version will be available online soon:

Main Asset Management Screen:

Asset Management, multiple categories and unlimitted levels.

Asset Detail Screen:

Asset managment, detailed screen with purchase, insurance details and classifications.

Preventive Service Schedules:

Preventive Maintenance Screen with Service schedules, services and tasks.

Service History and Forecast:

Preventive and Repair Maintenance, Service History and Service Forecast

Gantt Charts of Equipment Uptime and Service History:

Gantt charts showing maintenance and availability (uptime) of equipment.

For a freelance web developer or to enquire about ProEAM contact Erhard Smit on 083 656 4309 or email erhard@ernic.co.za

I’ve had the pleasure to go on a wild chase after a calculation bug in a stored procedure for one of my clients. A few hours later and lots of coffee led me to an age old user error when dealing with equations in SQL.

The calculation looked something like this:

SET @NewValue = @Rate * @FieldValue + 0.16

Now under normal circumstances, if @Rate is equal to 2 and @FieldValue is equal to 0 the result from the equation will be 0.16. To test use:

SELECT 2 * 0 + 0.16

But what happens if either the @Rate or @FieldValue is NULL, will SQL automatically convert the NULL to 0? The answer is NO. The resulting value will be NULL, not 0, not 0.16 as we would hope. So, to avoid this common mistake amongst SQL developers use the IsNull function around field with uncertain input values. The corrected statement should be:

SET @NewValue = IsNull (@Rate,0) * IsNull (@FieldValue,0) + 0.16

Every so often you run into situations where you need to schedule a stored procedure or SQL query to run at specific times, like a month-end process, batch processing, data archiving etc.

While some database engines like Microsoft SQL support job scheduling others don’t. I found that the problem with native schedulers in databases is that the scheduling itself is often very limited with only support to run the jobs on specific intervals, not specific times or vice versa. Whether your database supports job scheduling or not, let me show you how you can use Optitask to schedule SQL queries or Stored Procedures to execute at specific dates and time intervals.

First you need to create a task, then you need to add a SQL Task action and specify the database connection and the SQL Query to execute. In my example I’ll be executing a stored procedure called “MyStoredProcedureName” and pass the current short date as an input parameter. For long running queries or procedures make sure you set the Command Timeout to an appropriate value in seconds.

After your SQL Task action is setup create a new Scheduling Trigger to specify a schedule to execute the task.

For this task I specified a schedule to start at a specific date and to continue every two weeks on the same day and time.

Another popular feature that cannot always be done with native database schedulers is to handle errors when your Stored Procedure or SQL query fails. With Optitask you can specify “Error Actions” that will execute when your main task fail, in this case I specified an error action to send an Email to our administrator when the SQL task failed: