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
INSERT INTO @TEMP (uniqueId)
SELECT uniqueId FROM myTable
-- Start looping through the records
WHILE EXISTS (SELECT * FROM @TEMP)
BEGIN
-- 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
END

 

 

 

 

 

Cricket Scoring Signals and Symbols

Our 10yr old has started playing cricket this summer (we'll add that to his weekly sporting roster of tennis, indoor soccer, basketball etc). I've been roped in to score the matches for his team – and by roped in I mean there was a deafening silence reverberating around the assembled group of parents at the first match when our coach asked if anyone would like to volunteer.

Actually, it's quite a pleasant way to spend a Saturday morning, although of the first 5 weeks of the season 2 games have been washouts, 2 played in the cold and rain, with only the most recent last Saturday enjoying anything approaching what might be termed weather that one might expect for cricket, warm and sunny.

For my part I've been forced to rummage in my memory of how cricket works – my last exposure was as a 9 or 10 year old at boarding school in England, where I was considered such a liability to the school team I usually was appointed 'tally-wagger', a peculiar job description applied to the person who turned over the numbers on the score board at the behest of the scorers (see, I wasn't even allowed to keep the score, the limit of my expertise was considered to be tugging on the string that made the numbers flop over in turn).

It turns out scoring cricket is not so much maths, as frantic application of a pencil to a score book, where denoting even just a simple run requires annotations in three or four different places. And given these are little kids, who don't need much of a run up to bowl a ball, they rattle through the overs at a quick pace, so blink and you miss something. There are lots of symbols you need to be across in order to properly record each ball of each over, and the resulting score or outcome that might eventuate.

An opposition scorer, new to the task like me, has put me onto a handy cheat sheet of the signals umpires used to indicate what just happened, and the symbols used to record the outcome, from the NSW Cricket Umpires & Scorers Association.

 

iPad stencil for Omnigraffle

I've spent part of today scoping the screens for a new iPad application for a client. I normally use the wonderful Omnigraffle for this type of thing, so went hunting for iPad stencils for Omnigraffle and found:

This worked perfectly (just download, unzip, and place the stencil files into users/library/application support/omnigraffle/stencils.

These guys have a nifty app called Writer that I really must take a closer look now I know that one of my gods Stephen Fry knows its name.

 

Twitter sure handles a lot of messages

Been battling some Twitter code I wrote a few months ago that first posts a status update to a Twitter account using /statuses/update.json, then allows a user to retweet using /statuses/retweet/[tweetMessageId].json to another Twitter account.

When I tried the routine this morning it steadfastly refused to send the re-tweet. The Twitter api just returned 'Not Found'.

I tore all the routines apart, added debugs everywhere, without success. Then, staring at the returned JSON values, with the database open on another screen, I finally twigged.

When I post the Tweet I'm grabbing the message ID number and saving that to the database. Then, when I want to retweet, I use that message id in the retweet end point.

A couple of months ago the message Ids were 11 digits long, now they are 16 digits. I'd been grabbing the 'id' value coming back from Twitter, but a number that long was getting truncated by my JSON parse routine (I'm using the very handy JSON2 library). I checked the JSON values and Twitter very kindly also sends back 'id_str', which is the message id as a string. For example the JSON contained:

"id":1436631697457152

"id_str":"1436631697457152"

Indeed, now I look more closely, I can see that they also send back the ID of the Twitter account using the same, for example:

"id_str":"112303018"

I guess the explosion in the message Id in the past few months just continues to underscore the remarkable growth in Twitter's scale, presuming the message ID values have simply been incrementing sequentially over that time and haven't been jumped ahead manually for some reason.

 

Saving Voicemails and Text Messages to your Mac from your iPhone

Had cause today to want to retrieve and archive voicemails and text messages from my iPhone4 to my Mac. Hunted around a little bit – for example found advice that the only way to transfer a voicemail to my Mac was to plug in an audio cable from the iPhone's headphone jack to the Mac and essentially 're-record' the audio of the message.

That didn't sound too much like fun, then came across Decipher Media and their iPhone Tools. Isn't it nice when someone offers software that does exactly what you want, in a simple, understandable fashion, at a price that doesn't affect whether you eat for the remainder of the week.

They have two products, one called DecipherTextMessage, and another called DecipherVoiceMail. Bundle was $7.99.

You install the software to your Mac. You backup your iPhone (via iTunes). You run the programs and they retrieve your text messages and voicemails from the backup files on your Mac – note, NOT from your iPhone.

Interestingly not only did they find messages from my iPhone4 (which I've only had for a few weeks), but also my previous iPhone – whose backup is clearly still hiding away on my Mac.

For example, despite the fact I have long ago deleted many of the text messages, it retrieved an entire exchange of messages between myself and someone, starting from October last year.

You can save the text messages out as a text file – so easily searchable. And save the voicemails out as MP4.

If you are engaged in some kind of dispute, negotiation or other action where you want to ensure you are maintaining robust records of communications I reckon $7.99 is a tiny price to pay. For a start would have thought lawyers should have this by default.

Not sure there's a Windoze version though for all of those people who have not converted to the alter of Jobs.

 

 

Taking screenshots on iPhone and iPad

It's the little things that you learn that make life easier. I wanted to screenshot the RushCrowds iPhone app this morning, turns out is easy:

  1. Open the screen you want to copy
  2. Hold down the power and the home button at the same time for a second then release, the screen flashes white and you hear a camera click. (The power button, which might be officially called the sleep button I suspect, is the one at the top right, the home button is the round button at the bottom middle).

It saves the screenshots to your Photos, so you can email them, or just sync over to iPhoto.

 

CyberSource Payment Gateway – Authorize and Capture

I've been implementing a credit card payment gateway recently using the Cybersource system and their SIMPLE API features – it's a COM object based system.

Everything seemed to go swimmingly well until I struck a snag processing Refunds using the follow-on service, which is where you can refund a previous payment by referencing its unique identifiers. As opposed to a stand alone refund credit.

Cybersource steadfastly refused to allow the credit, giving me errors saying that I was missing a data field, which, after spending a bunch of time carefully checking the fields against their SDK documentation, I was convinced was not correct.

Finally I worked it out – and it had been staring at me in the face. Credit card systems generally have two modes:

  1. Authorization – where you check that the card number etc is valid, and that the customer has the bucks available
  2. Capture – where you then 'capture' the payment based on the Authorization

Virtually every credit card gateway I've used wraps Authorization and Capture into a single function for cases where you just want a straightforward payment. EG you just want to get the customer's money in a single transaction.

Cybersource is different, first you have to Authorize the payment, then Capture it, which of course you have to do as a single set of calls all as part of processing the customer's payment. On the surface of course it makes complete sense, you are replicating the actual process going on behind the scenes.

But in practice it means making two separate calls to the Cybersource API, all while the customer is sitting watching the spinning 'loading' icon in your shopping cart page.

Matters are not assisted by Cybersource's documentation not clearly describing this (a flow chart or diagram would be helpful), and their samples are woefully deficient – they don't actually have a sample that shows a combined Authorize and Capture scenario – which would surely have to be the single most common activity undetaken by developers implementing their API.