Looping through records in SQL Server Stored Procedure

Someone who’s pretty good programming on the front end, but not so crash hot on the backend, eg SQL Server, asked me the other day about looping through recordsets in a SQL Server Stored Procedure.

It is of course possible to use a cursor and FETCH but most SQL programmers frown on cursors in stored procedures. Cursors involve grabbing a bunch of records and locking them, there can be a substantial performance hit. They should really only be used as a last resort, for example, where you don’t have a good enough unique key value on your rows (although there are ways around that one usually as well).

The easiest way I know to look through records is to employ a temporary table, here’s a simple example:


-- Create a temporary table to hold the records to be updated
DECLARE @uniqueId int
DECLARE @TEMP TABLE (uniqueId int)
-- Insert into the temporary table a list of the records to be updated
SELECT uniqueId FROM myTable
-- Start looping through the records
-- Grab the first record out
SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100))
-- Perform some update on the record
UPDATE myTable SET myField = 'something or other' WHERE uniqueId = @uniqueId
-- Drop the record so we can move onto the next one
DELETE FROM @TEMP WHERE uniqueId = @uniqueId






How to Set Consultancy Fees

Came across this fantastic nugget the other day:

consultants set their rates by the project. They estimate the number of
hours they expect to spend on a project, then multiply by their hourly

some consultants set their project fees using the value the client
derives from the consultant’s advice. There’s an old joke about
physicist Niels Bohr that illustrate this principle.

company’s machine breaks down. The company’s owner, an old school chum
of Niels Bohr, calls in the physicist for help in fixing it.

Bohr examines the machine. He draws an X on the side and says, "Hit it right here with a hammer."

company’s mechanic hits the machine with a hammer. It springs into
action. The company’s owner thanks Niels Bohr profusely and sends him
on his way.

A few days later, the owner receives an invoice from Bohr for $10,000. Shocked, the owner phones Bohr!

"Niels! What’s this $10,000 invoice? You were only here for 10 minutes! Send me a detailed invoice."

Bohr agrees to send the invoice. A few days later, the company’s owner opens a new invoice.

Drawing X on the side of your machine                             $       1
Knowing where to put the X                                             $ 9,999
Total                                                                             $10,000

HP All in One Printer and Snow Leopard

Went to scan a document yesterday on the HP Photosmart 3310, but my Mac couldn't see the scanner – the pop up scan window kept saying scanner not found.

Played with it for ages, using the HP Device Manager thingy, but apparently the scanner was gone. But could print fine.

Finally found this document on the HP site. What do you know. The Snow Leopard upgrade breaks the HP scanner system.

But not to worry – because now it's all built into Mac, you can scan direct from Preview (very handy, straight to PDF which was what I wanted in the first place); Image Capture; or just from the Print and Fax settings.

You'll need to delete the printer from System Preferences > Print and Fax, and add it again, but that's the work of a moment.

Despite the initial frustration, actually very happy. But damn it's annoying when something simply disappears after an update and you have to go hunting for a solution. I did check for updates on the HP software and it said it was the latest version. What would be nice was if HP pushed an update that alerted you to these changes.

Other projects will pay for hare-brained scheme – NOT!!!!!

Just been reading Kenneth Davidson “Other projects will pay for Rudd’s hare-brained scheme” in The Age today. I think he is just plain wrong in his assertion that “the high-speed broadband network should be built incrementally”. Using his arguments the gas and electricity companies wouldn’t install pipes and wires to my house until I discovered I needed to have a shower or turn on a light.

He misses the point entirely. Innovation over the centuries has been driven by bands of individuals coming across a service or infrastructure and creating new and exciting purposes and products.

Staying in the technology sector for a moment, both the Apple iPhone Apps Store and Facebook Applications systems were infrastructure ideas created by the companies without a complete vision of what would result. But they were visionary enough to comprehend that by constructing a framework that enabled developers across a broad range of competencies and resource availability to create new software, they could stimulate a world of innovation. And that’s exactly what has happened. Both companies now have available tens of thousands of services and applications produced by third parties. Some of the programs are terrible. Others are striking in their innovation. All that was required was a faith in the inventiveness of their customers.

We started our internet company in our lounge room in 2000, back before broadband was even available to us. We sticky taped solutions together at almost no cost using what was available. Six years later we sold the company for several million dollars.

Our innovation today still emanates from our loungeroom – we have web servers in the family room cupboard. We constantly play with the available technologies to investigate how they can be alternatively utilised or reorganised to produce new products and services. Under Davidson’s regime we would not have access to the new infrastructure – not being a big company, or hospital or university.

I notice Davidson has left schools off his list of those privileged organizations who should receive preferential treatment. My eleven year old daughter, who runs online forums, writes on her blog and is constantly searching for new ways to use her internet connection and computer will be very disappointed. He’s ignoring one of the most innovative groups in our country – the kids who, by the time the NBN is finished in eight or nine years, will be moving into the workforce, starting businesses, and creating community and economic value.

History also shows us that major projects, particularly technology based,  rolled out incrementally are rarely completed. Progress slows, attention and resource is diverted, specifications and impetus and politics divert from the path.

Kevin Rudd’s vision on this is bold to be sure. Yes there are massive challenges. But the technology is proven. And put that technology in the hands of individuals and not restrict it to the privileged few, and the aggregate can only benefit Australia and the rest of the world.

Niche Content Millionaire

090406 Book Cover 200px

Just putting the final touches to our new eBook, Niche Content Millionaire and sending drafts off to various friends for review.

Niche Content Millionaire will be released this month. But you can make sure you secure your copy by registering now.

a cool experience when you’re sitting in a lawyer’s office high up in a
down town office block and someone casually hands you a check for a
couple of million dollars. It’s even more cool strolling into the
nearest bank branch and depositing the check – then rushing out to the
ATM to check your account balance.

We’ve been involved
with niche online content as a business since 2000. In that time we’ve
launched commercially successful web sites in Australia, the USA and
the UK. Our sites have attracted tens of thousands of subscribers, and
many times that number of casual visitors. In late 2006 we sold the
largest of our businesses for more than $US1 million.

book is not designed to be a text book, nor a biography, rather it’s a
distillation of our story, married with advice and ideas that we
believe assisted us along our journey to success. We learnt our lessons
the hard way. We hope that you will read the book, learn and then be in
a position to apply those lessons to your own online, or offline,
business venture.


SEO Secrets

People who know me, or who browse my blog and various writings, will know I'm not one for hawking products. But finished reading an eBook by fellow Australian Glenn Murray called 'SEO Secrets' and am making an exception. People who know me may have also heard me on my SEO (Search Engine Optimisation) soapbox.

I tend to lean towards the notion that paid SEO is the dark arts. I've worked with a bunch of clients who have paid companies for SEO work, and none of it has impressed me. I had one client last year forking out $400 a month, and the firm had basically broken every rule in the Google book – hidden links on the home page (reams of them); white text links; using a flash page to hide SEO text – you name it. On my advice they dropped the SEO firm, and we built a whole new site, using tried and true conventions, and hey presto, they rank as well or better for their keywords. And save $400 a month.

I fell over Glenn's book when looking for something completely unrelated, I was hunting around for good examples of an eBook being sold online in preparation for Fiona and I releasing our book (more on this in a week or two I hope). I liked his site, I liked he was Australian, I liked the fact that he really must be a copywriter because his sales pitch is the complete antithesis of the hyped up high pressure rubbish you so often see by the con merchants flogging their tired old paid information booklets.

So I did what I almost never do, and bought the book. And am glad I did. The majority of the content is not a major revelation to me, because I've been building sites for a long time, and generally feel I'm across the big issues with regard to coding and optimising content on a site. We kinda did this all before at our previous www.artshub.com.au business – with literally hundreds of thousands of pieces of content on the site network it became second nature.

But Glenn's book is a clear, well written, concise confirmation of my prior knowledge, plus throws in a few nuggets of 'oh of course' information that I know will prove useful in the future. And all nicely organised in a format I can easily dip into as required.

If unlike me you are not across the SEO issues, but you have a web site, and you want to know what all the SEO fuss is, then I reckon this is a good purchase.

Glenn concludes his book with:

"Search Engine Optimization is not a black art; it’s a science. There are defined rules and proven methodologies."

OK, I'm going to hold to my assertion that it's still the dark arts. There are so many variations, permutations and circumstances that, when teamed with the fact you are dealing with unknown Google algorithms, there is no guarantee. But Glenn has done a good job of laying the known facts on the table, and offering a clear path through the maze.

You can buy Glenn's book from his web site by clicking this link. And yes, I'll be upfront and say I've signed up to his Affiliate Program and yes I get a commission.