Over the last few months I have occasionally wandered over to Microsoft Learning to have a look at the 'new' certifications.  How can certification that has been available for months on a development platform (.NET 2) that has also been out months be considered new?  Apart from the lack of study kits and other logistical issues, it seems that most developers and employers don’t really care about the Microsoft certification.

It seems that there is a negative attitude towards certification that has been propagated by the concept of a 'Paper MCSE' where a ranter will say something like "An MSCE used to be worth something, now training institutions are churning out paper MSCE's" – implying that there are a whole lot of people out there with certification that cannot code.  I have interviewed hundreds of people over the years and always find myself needing good resources on any project that I am on – requiring constant interviewing and screening.  Funny thing is that I have never come across a 'paper <insert certification here>', but I have come across many useless developers.  It may not be statistically meaningful that of the last hundred development candidates that I have mentioned that there was not one that had certification and couldn't develop – but it would raise a statistician’s eyebrow.

Microsoft is apparently trying to revamp the certification by breaking it up into little bits and making it 'easier' to obtain certification aligned with job functions – something like an n-tier approach to certification (you shouldn't need certification to get that analogy).  Maybe they have a long way to go to convince the market that certification is required, but I think they have made a step in the right direction.

I will be the first to acknowledge that (correct) experience is the ultimate prerequisite for a developer and when interviewing, it is something that I will drill in to.  However, even 'experienced' developers can have huge gaps in their knowledge – depending on the projects that they have been on, a developer with a claimed two years' experience may in fact have one month of experience repeated twenty four times.

<rant>Don't get me started on bodyshops - that pound you with CV's of unqualified, uncertified contractors that they expect you to filter out.  Why, for the premium that they charge, don't they get their contractors certified - apparently they are so good that it would be a breeze.</rant>

If you think that certification is a waste of time, take this little challenge.  Have a look at the preparation guides for two exams for a Microsoft Certified Technology Specialist (Web Applications) (the lowest level of certification), namely the Application Development Foundation exam and the Web-Based Client Development exam.  Scroll down and see how much you know on each topic.

Done? Good.

Now if you are a team leader, architect, project manager or other senior role ask yourself the following questions:

• Would it be better for my project if *all* the developers on my team even vaguely knew all of those topics?
• How many of the developers on my team could answer questions on those topics accurately?

If you are a developer as yourself a more simple question. 

• If I was being interviewed would I squirm like a worm on a hook if the interviewer asked detailed questions on all that stuff?

If you had positive 'no worries mate' answers to the above questions, then you are on a unique team and will be the envy of many.  If you had negative answers, then maybe you should review your position on certification and think about using it as a standard – either to find developers or to pitch yourself as a developer.

Personally, I can't think of any developer that I have interviewed, including 'senior' developers, who would not be taken out by most of the topics on those preparation guides.  Are the preparation guides biased by Microsoft and not representative of what a developer truly does?  I think that a 'senior' developer should at least know about every single one of the topics in the 'Technology Specialist' series – what with it being the lowest level.  Unfortunately few ‘senior’ developers do.

One of the ideas of certification is not just to provide the individual with knowledge, but to provide a standard so that everyone is on the same page.  In a recent quality review, when challenged on simple things like naming, error handling and so on – a developer commented something like "It (quality review of private methods) is not necessary because we are all good developers and write good code".  "By whose standards?" I asked "Are you certified?" (shake of head) and pointing to the developer next to him "Are you?" – another shake of the head; and so on around the room.  I have my own personal opinions on how those developers would handle me grilling them on those preparation guides and the expectations are not very high.  Not that they are bad or even mediocre developers, but I do need to spend a lot of time introducing them to the basics – to overcome the "one-month-of-experience-for-twelve-months" problem that I have.

If you are an uncertified developer, why are you not certified?  Is it because you are waiting for your employer to send you on training and give you permission to go to the toilet?  Are you scared that you won't pass?  Well then study and make sure you pass!  Are you a better developer than everyone else and above certification?  Well then step into the ring and prove it to us!  Are you worried that you will be lost amongst all the 'Paper MSCE's'?  Get your head out of the sand, there aren't any!

Regardless of the technical value of certification, it is only meaningful if people who are qualified to be certified, are certified.  There is no point in putting out a job advert that requires certification if nobody is certified – no applicants will come through.  The converse is true – why get certified if no employers even look at it?  As many people need to be certified as possible and in the .NET space I think that the Microsoft certification is good to get. 

If, after reading this, you think that such certification is worthwhile then go out and get it (as a developer) and start demanding it (as a leader/manager) – only by changing the dynamics of supply and demand will anything of value evolve.

Simon Munro

www.delphi.co.za

When writing complex sproc’s in T-SQL you can sometimes run into the problem that IDEs and other tools, while trying to parse the query, are unable to realise that the query actually works.  This is because they try and interpret the query to get the metadata (the format of the result, rather than the result itself), rather than execute it – not such a bad thing since you don’t want an IDE to execute a sproc willy-nilly, which could result in all sorts of side effects.

The main place where developers will encounter this is when trying to configure a TableAdapter (or something) that uses temp tables.

Consider the following sproc:

CREATE PROCEDURE [dbo].[DoSomething]

AS

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

Executing this procedure via SQL renders the expected result as follows:

(1 row(s) affected)

Id          Name

----------- --------------------------------------------------

1           Name1

(1 row(s) affected)

However if you try and create a TableAdapter in Visual Studio using the same sproc, you get an error that looks something like this:

The short solution is not to use temp tables, but rather table variables – which for lots of other reasons is arguably better.  The altered sproc would then look like this:

ALTER PROCEDURE [dbo].[DoSomething]

AS

DECLARE @MyTable table (Id int, Name nvarchar(50))

INSERT INTO @MyTable

VALUES(1,'Name1')

SELECT * FROM @MyTable

That is fine, but occasionally you may need to use temp tables and developers land up creating ‘permanent’ temp tables or some other approach.  The question remains, how can we force Visual Studio to ignore the metadata and actually execute the sproc?  In some cases temp tables may be unavoidable – particularly if you are using someone else’s sproc.  The quick solution is to use the SET FMTONLY statement which when turned off would seem to return data as well as metadata to the client.  The SQL Books say the SET FMTONLY ‘Returns only metadata to the client.’  I am making the assumption therefore that setting it to ‘OFF’ returns something else as well – I suspect it is the data.

So, to use a sproc that uses temp tables in Visual Studio, the sproc could be changed to:

ALTER PROCEDURE [dbo].[DoSomething]

AS

SET FMTONLY OFF

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

… making Visual studio happy.

Interestingly, SSIS (SQL Server Integration Services) has an additional quirk.  Firstly, if you use a temp table without turning FMTONLY to OFF, and write a source query of ‘EXEC DoSomething’ you receive an error message ‘This SQL statement is not a query’ – SSIS doesn’t even parse your carefully constructed SQL.  You know that

1. It works in the query analyser
2. It is a query
3. You are more intelligent than your PC, so if you know it is a query then it must be a query.

The best way to convince SSIS that the query is actually a query is to insert the SET FMTONLY OFF line into the sproc (without changing the source query that SSIS uses – see it was a query!).  You may think that the solution is the same as with Visual Studio (use table variables), but SSIS was written by some other development team and has a different attitude and personality.

If you execute a sproc with table variables or temp tables with FMTONLY OFF you get something like the following:

If you look carefully, the ‘Executing’ successful (is a valid query) but the ‘Pre-Execute’ fails.  I tried to turn off the pre-execute as an option in the designer with no success.  The quick answer is to add SET NOCOUNT ON to the sproc, which for some reason lets the pre-execute run successfully.  I am not sure why, the SQL Books say that NOCOUNT ‘Stops the message that shows the number of rows affected by a Transact-SQL statement from being returned as part of the results’, which doesn’t seem relevant to pre-execute – but it does work.  The query would then be changed to:

ALTER PROCEDURE [dbo].[DoSomething]

AS

SET FMTONLY OFF

SET NOCOUNT ON

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

Or, if using table variables:

CREATE PROCEDURE [dbo].[DoSomething1]

AS

SET NOCOUNT ON

DECLARE @MyTable table (Id int, Name nvarchar(50))

INSERT INTO @MyTable

VALUES(1,'Name1')

SELECT * FROM @MyTable

In summary:

1. Rather use table variables than temp tables
2. When using temp tables use SET FMTONLY OFF
3. When using SSIS, use SET NOCOUNT ON
4. If SSIS says your query is not a query it may be trying to fool you.

Simon Munro

I have been interviewing for developer positions this year and although I have been accommodating of the ramp-up from .NET 1.1 to .NET 2.0 I feel that now, six months after the release of .NET 2.0 and Visual Studio 2005, that it is time for developers that are presenting themselves to the market to have .NET 2.0 skills.

On our project we were lucky to start development this year and are using full-bore .NET 2.0 and in this green fields project, the use of the new technologies makes a major difference.  It seems that just about every class we use has the ‘Note: This class is new in the .NET Framework version 2.0’ message at the top of the window when looking for help on MSDN.  I think that our project, with it’s recent start date, uses .NET 2.0 more than your average project or development shop that is migrating to the newer framework.  This means that when interviewing I am looking for *some* .NET 2.0 and VS2005 skills so that the learning curve for new developers on the team is not three to four weeks – before even touching the project.

By .NET 2.0 skills, I don’t mean that they have to be experts – with six months of real project experience on .NET 2.0 – but at least need to have ‘played around’ with the new stuff and have an opinion on what the main changes are and how it affects development.  For us, the changes are significant and the mindset of the developer needs to be in tune with those subtle, but far reaching changes.  We use, for example, well-structured strongly typed datasets and although in theory it is pretty straight forward, it makes a big difference to how we code and interface with the database.

Different projects and organizations have their own pace with which they move to the latest version on anything and there are many good, and practical, reasons for not tearing the shrink-wrap off a product and implementing it with wild abandon.  My concern is not with the well-established projects with an architecture which has a sound base in .NET 1.1.  My problem is that individuals are marketing themselves as .NET developers and don’t have a clue as to how things would work differently in .NET 2.0.  Even worse are the body-shops and development shops who are taking these developers from .NET 1.1 projects and pushing them into new projects without the tiniest smidgen of training in the newer technologies – something they should at least attempt for the rates that they bill their resources out at.

So, any developers out there who haven’t gotten around to learning .NET 2.0;  firstly, you are missing out on something that is cool and interesting. Secondly, if you are stuck on a .NET 1.1 project, please try and keep your skills up on .NET 2.0 for when you find yourself looking for another project or employer – at least your skills will be aligned to the market demands.  It is fairly simple to get up to speed – there is free training available from Microsoft for which you don’t even need VS2005 and there are also the express versions of Visual Studio available for free.  You should be able to learn enough in a few days/weekends/late nights.

To put it bluntly, if you are trying to get a job or contract and you don’t have some .NET 2.0 and VS2005 skills it indicates that you are either lazy or have a tenuous grasp of .NET anyway.

Most people reading this post will have some up-to-date skills or else they wouldn’t have found the post but unfortunately Huisgenoot and You magazine (which must be all you are reading if you haven’t heard about .NET 2.0) don’t really cater for technical blogs.

Simon Munro

PS: If you are a .NET developer looking for a contract on a .NET 2.0 project, let me know – I have an opening for you.

My previous post ‘The Missing Link - a Platform Developer’ was in part inspired by deployment efforts that I have been busy with over the last few weeks.  I found that this link on Scott Guthrie's Blog (a blog which if you are not reading frequently – you are simply not doing ASP.NET development) resonated my need for a Platform Developer.

The image below reminded me of how much developers need to understand deployment issues and the deployment platform.  It seems that Microsoft, in an effort to make deployment easier, is blurring the line between what is the responsibility of the developer, versus what facilities has to worry about.

You will see in the screenshot, icons for membership – something which is currently part of the development space, but being so integrated into IIS admin functionality it is becoming part of facilities.  Look at all the other properties, which were previously part of the development teams’ responsibility – connection strings, session state etc.  Final clues are that in IIS7 the administrators can ‘delegate administrative control to developers or content owners, thus reducing cost of ownership and administrative burden for the administrator’ (it seems that developers do not have enough of a burden) and there is a .NET API for IIS 7 too.

So if there are any aspiring Platform Developers out there, deployment of applications on IIS7 is definitely something to pay attention to.  Project Managers and Architects, if you don’t want to surrender your architecture to the ‘burdened’ administrators – identifying a Platform Developer in your organization to focus on IIS7 is probably a good idea.

Simon Munro

Microsoft certification for a web developer deems it necessary to master the membership API available in ASP.NET 2.  While the new membership model is a useful set of controls it is not necessarily on my list of have-to-knows for a web developer – if a lone developer is churning out lots of little websites then it probably is critical knowledge. However, in the context of a large project consuming thousands of man hours it is not a big deal – the login page and related bits can quite easily be handled by one developer in a few days while the rest of the team develops functionality that is on the business requirements checklist.  It does make one wonder about Microsoft’s intended market for certification or ASP for that matter – but that is an entirely different discussion.

It begs asking the question then – What is on your list of have-to-knows?  It is a question that doesn’t really have a simple answer since I would like developers on my team to know things that are a) useful and b) aren’t known by others on the team.  Although it depends on the type of development – building software for Mars rovers requires a different set of skills than for a shopping site – I’m biasing this towards the more mainstream practice of developing applications that serve a business need and generally have a database of sorts where data resides that has to be viewed, captured and processed. 

In such an environment, the primary set of skills that we need in the team is the ability to work with a fairly narrow set of components, tools and technologies and in the Microsoft web space that would be SQL, ADO, Web Pages, Grids, data binding and so on.  The ‘senior’ developers tend to be allocated larger chunks of  (more difficult) functionality and are left on their own and juniors will pick up the scraps.  In most corporate development teams there is little space for developers with too much flair or hotshot developers with little experience in corporate projects as they tend to clash with the architect, project manager, testers and others that have to keep their eye on deadlines.

So if we have turned our developers, junior and senior, into cogs of a well oiled delivery machine (which should not be seen as a bad thing), then who do we get to work on all the other stuff?  What ‘other stuff’?  There is more ‘other stuff’ than most of us can possibly cope with – as the technologies become more complex and our solutions demand use of those technologies – we find that we are working with fairly difficult stuff, whether or not it is abstracted by tools or components. 

By way of example, I am currently working on a solution that has some interesting security quirks and requirements and if you spend any amount of time trying to figure it all out you soon realise that the complexity of the domain has increased since you last looked at it (even if that was yesterday).  Gone are the days of closing some ports of the firewall, you now have to implement all sorts of services, certificates, authentication mechanisms and so on – all to ‘enable the mobile worker in a Service Oriented Architecture’ (or some similar far-reaching, buzzword laden architectural statement).

It seems that while the developers are focussed on delivering the business functionality, there is a vacuum of people who can even understand the acronyms, never mind having working knowledge, of the particular domain.  So a lot of this work is left up to the multi-portfolioed architect who investigates, fights with documentation, codes up samples, Googles and whatever else is necessary to establish the fit of the technology into the architecture.  The architect is apparently the best person to do this because they have a better understanding of the general principles, experience of something similar (or a predecessor) and is technically strong. 

The downside to this is that the architect is already overloaded and once he has the technology mastered is seems logical that he codes up the entire solution anyway; ‘You’re nearly there, so just finish it up!’ (as undoubtedly minuted in the project management meeting) .  Often, if it is more once-off, it does not seem practical to handover to another developer, never mind transferring the knowledge to the whole team.  While the short-term benefits of dumping this work on the architect are appealing, over time the sustainability of the team suffers and the project can start to decay.

Another mistake that software development teams tend to make is that when it is questionable as to whether the technology is the responsibility of the development team or facilities – the developers palm it off onto facilities.  It is scary how few developers can even deploy their applications into a production environment or how much DBA’s have to analyse SQL statements to find the best indexes to put on tables.  This creates a problem where the developers are only vaguely aware of how their applications execute – not a good place to start if you want to create quality software.

While good technical leaders are able to hand out ‘research’ tasks to developers, it would be great if the architect had access to a more clearly identified individual who could figure out a few things on the architect’s behalf – this is the missing link that I am looking for.  A developer who is technically strong but may not have the other attributes, such as leadership qualities, to become an architect – but is able to get their head around complex technologies in well-scoped, bite-sized chunks.  I’ll call this role a ‘Platform Developer’ as most of the technologies that need to be understood are at a fairly low level (closer to the platform).  I am specifically not wanting a technical specialist, or even worse a ‘technology evangelist’ who is so wrapped up in their specialization that they suffer from architectural tunnel vision.

I think a key attribute of a Platform Developer is that he/she should be a ‘roving’ developer – moving between projects.  This is a great way to spread the knowledge about the technologies across the teams and also prevents them from gaining too much business knowledge in a project and becoming ‘indispensable’ according to the project manager.

I will leave it to comments and future posts to define this role further, but first let me suggest some ways of creating these Platform Developers, or at least finding ways of identifying and using those that exist already.  Everyone has a role to play…

• Organizations 

Find a way of communicating this need and rewarding individuals.  Something as simple as allowing them to use an MVP-type acknowledgement in their email signature specific to your organization, would work well.

• Project Managers 

Plan up front for use of consulting or cross-team resources in a consulting engagement style.  Remind the architect that there is budget available for this kind of help.  Most important – if they are borrowed from another team, give them back! Project managers hijacking resources is a bad disease.

• Architects 

Communicate, mostly informally, the things that are on your mind to investigate and allow developers to show stuff off, even if it appears to have no architectural fit.  Don’t always try and be the single, central repository of technical knowledge.

• Developers 

Speak to the architects about their need-to-investigate lists and choose a technology that you are interested in and comfortable.  After a five minute discussion with most architects you will probably land up with about five years of investigations that you can do – so choose carefully.  Try and choose something that is a bit different from your current skills – you don’t want to become too much of a specialist and paint yourself into a career corner.  Along the way, present (either formally or informally) your findings to one or more architects but remember – don’t force it into the ‘current’ architecture; allow the architect to determine the fit into their brainchild.

In summary; if for example, I want to know how to ‘Authenticate a windows application against active directory and pass the token to the web service…’.  I don’t want blank stares from the senior developers or ‘about 1,600,000’ Google matches.  I would like someone on hand who can help me out.

Simon Munro

Arno Nel has blogged twice raising the question ‘What is Architecture?’, so I’ll take the bait.  There are many different definitions of architecture with everyone chipping in - IBM, Agile, Martin Fowler (who unsurprisingly differs with every else’s definition) … there’s even a IEEE Standard to define architecture!  So why all the confusion?  I think that most of the people reading this are actually more concerned about the role of architect, rather than the formal definition of architecture.  If you have been performing architectural functions for while and implementing good architectures, you have a pretty good feeling of what architecture is, regardless of the formal definition tossed up by a big blue vendor, an outdated standards organization or a bunch of academics in tweed jackets.

The reason we want to know what an architect is, is because there are lots of jobs for ‘architects’ and they earn the big bucks.  Search on a job site for ‘Software Architect’ and you will see plenty of opportunities at high rates – so why not figure out how to be one?  Microsoft has perpetuated this problem by trying to be the first to certify software architects in their Microsoft Certified Architect Program where techies with ten years of experience on any platform (yeah right – from Microsoft) may or may not be accredited by a panel of their peers at a cost of around $10,000.

To me, a bigger problem than defining the term ‘Architecture’ is coming up with a better term for the technical hero on the team who is ultimately responsible for the way the the system looks, feels, smells and behaves (all within time and budget of course); more colloquially known as the Architect.  When I am in an ‘Architectural’ role I find myself designing, coding, helping developers, investigating technologies, understanding the requirements, helping with testing and deployments, providing estimates, talking slowly to the project manager, interviewing resources, optimising SQL statements, and so on.  It looks like those functions go beyond architectural functions and cross many disciplines in software development.  I generally know the detail of how a particular business function has been implemented (even if I didn’t implement it) and can advise developers wanting to add functionality. Sounds like a bit more than 'architecture'.

Some discussions around architecture reflect this problem where architectural discussions are peppered with processes and statements such as ‘..extreme does not work well within an enterprise architecture’.  If architecture relates to how the bits and pieces fit together, how did we get to a discussion about development processes?  The answer is simple really; Architecture is affected by external factors and stakeholders and a good architect always knows where to compromise the ‘perfect architecture’ with useless users, under skilled developers, scarce test data, small budgets and whining executives.  So the architecture that we land up with reflects all the non-technical bits and pieces – development processes amongst them – but it doesn’t mean that a development process is any more part of the architecture (in a formal sense) than the guy with the pointy hair.  The Microsoft Certified Architect Program looks quite good to me and it reflects the functions that are not formally considered part of architecture with criteria such as leadership, organization dynamics and communication.

The amount of pure architectural work that an architect will do depends on the size and makeup of the project.  If you are the ‘architect’ on a smallish team where you spend four days fighting with VSTS to get it installed properly, you are hardly doing ‘Architecture’, but you probably are the one who designed the system and even if you’re not officially taking responsibility, it is highly likely that it is your head that will be rolling if things go wrong.

My quick definitions:
Architecture : The way the system ‘looks’ at the end of the project
Architect: The person who made it that way.

Simon Munro

I’ve been suffering recently from TDS (Telecommunications Deficiency Syndrome) which is endemic to South Africa and is caused by Telkom.  This has affected my non-work web browsing, subsequent posts and general lurking on the Internet.

The summary of the problem is that I moved within my townhouse complex from one unit to another and six weeks later I still have not been able to move the ADSL line.  Funny that it is possible to organise a truck, movers, cleaners, boxes and cats when moving house, but it is impossible to get a guy in a Telkom truck to ‘re-patch’ four wires in a distribution box at the complex – even with a couple of weeks’ notice.

I could rant and rave, describing the weeks of frustration that I have gone through, but since the problem is endemic everybody has the same problem and I have nothing new to add.  No-one is really interested in hearing yet another rant about Telkom and neither do they care – particularly Telkom.  Talking about Telkom problems is like talking about the weather… a conversation filler when people have very little to say to each other.

(Jack and Bob are ex colleagues who meet in the frozen food section in Woolies)
Jack: Hi Bob, how are you?
Bob: Fine, and you?
Jack: Can’t complain.  How are you wife and kids?
Bob: They’re fine, but have had the ‘flu.
Jack: I can imagine, with all this weather!
Bob: Yes, it is unseasonal.
Jack: How are your Telkom problems?
Bob: The usual - high costs, no service.
Jack: Yeah, same here.  Been down for the last two weeks.
(uncomfortable silence)
Bob: (Shuffling) Anyway, my ice-cream is melting – gotta go
Jack: Ok, Bye. Good luck with the ‘flu
Bob: Thanks.  Luckily it won’t last forever. Bye.

It seems strange that Telkom made such a big deal about www.hellkom.co.za – does anybody really think that such a website will do anything to tarnish Telkom’s reputation?  Is it even possible to tarnish something that doesn’t exist?  Every Telkom consumer knows how useless and disinterested Telkom is – trying to start a negative publicity campaign about Telkom is like trying to start a group to communicate how much of a moron Mugabe is – no one will fund you as we know already.

I hope that you get through your current TDS with as little suffering as possible.

Simon Munro

I have been looking at parts of SQL Server 2005 since the June CTP and been implementing SQL 2005 in production and development environments since December last year.  The difference between investigating the features and doing an implementation is that in an actual implementation you use the most practical and useful features, rather than the cool features that you may use one day in some future architecture.

SQL Server Reporting services is cool and useful - but is a separate bundled package - so I consider it a new product, not a new feature.  Similarly with SQL Server Integration Services - a great step in the direction of visual programming and DSL's (Domain Specific Languages).  Other features are intriguing and I intend to put them to use on some future architecture; the Service Broker, Database Mirroring and CLR Integration are some of the features that pique my interest.

Currently there are two things that I used immediately and so extensively that I almost can’t imagine what it was like developing for SQL Server a few months ago – those two are Schemas and Error Handling.  I would have to say that the addition of try-catch block has made the biggest difference.

T-SQL is often viewed as a second-rate language compared to C#, Java or other modern (or at least Object Oriented) languages.  However the set-based bias of T-SQL has always meant that a lot of functionality can be developed in stored procedures – some would argue too much.  The addition of the try-catch block brings T-SQL closer to being a more elegant and usable language.  Handling errors, particularly within the scope of transactions was difficult and cumbersome prior to SQL2005 – the need to inspect @@ERROR after every statement meant that the code was either riddled with too many error checks and conditional statements to exit elegantly or had insufficient error handling that would leave data in an inconsistent state.

Although not necessarily a candidate for a ‘best practice’ award, I have a chunk of boilerplate code that I put in virtually every stored procedure – allowing me to handle errors gracefully and to roll back transactions.  This code even reports the exact line number that an error occurred (including errors that occur in triggers or nested stored procedures)

BEGIN TRANSACTION

BEGIN TRY

  -- Do stuff

END TRY

BEGIN CATCH

  IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION

  DECLARE @ErrorMessage nvarchar(4000)

  DECLARE @ErrorSeverity int

  DECLARE @ErrorState int

  SELECT

      @ErrorMessage = N'Error in '+ERROR_PROCEDURE()+N' (Line '+CAST(ERROR_LINE() AS nvarchar(20))+N') : '+ERROR_MESSAGE(),

      @ErrorSeverity = 1,

      @ErrorState = ERROR_STATE()

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

IF @@TRANCOUNT > 0

  COMMIT TRANSACTION

It may not look like much and there are far more sophisticated uses of exception handling, but putting the above code into your stored procedures will increase the quality of your code and the integrity of your database by orders of magnitude for virtually no effort.

Simon Munro