THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

[T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

I recently returned to a client at which I last worked back in 2006 and, as you might imagine, that gives me ample material for a blog post dedicated to Crap Code; conveniently the subject of the latest T-SQL Tuesday.

I first worked for this client back in 2004 and that is significant because back then we were writing code on SQL Server 2000. Ah, thems were the days!!! I smiled wryly to myself when I encountered the following code in a stored procedure that I once wrote:

--Simplified for convenience/demo
CREATE PROCEDURE [dbo].[usp_PersonInsert]
(
  
@pXMLDataInsert     TEXT
)
AS
BEGIN
   DECLARE
@vDoc            INT
   EXECUTE
sp_xml_preparedocument @vDoc OUTPUT, @pXMLDataInsert
  
SELECT
      
ID,
      
Name
  
INTO    #Insert
  
FROM    OpenXML(@vDoc, 'NewDataSet/Table', 2 )
  
WITH    (
      
ID  INT     'ID',
      
Name    VARCHAR(70) 'Name'
      
)
  
EXECUTE sp_xml_removedocument @vDoc
  
INSERT INTO dbo.Person(ID,Name)SELECT ID,Name FROM #Insert
END

If you have never had the pleasure(!!!) of dealing with XML data prior to SQL Server 2005 then you're probably looking at this with a slightly confused look on your face wondering quite why we had to make this so complicated. TEXT datatype? OPENXML? System stored procs? What's in this @vDoc variable? On the other hand if you have had to deal with XML pre-2005 then right now you're probably nodding sagely to yourself and remembering the not-so-good-old-days!

No-one would ever write code like this today but back in 2004, before wonderous things like the XML datatype and table-valued-parameters, this was how one passed datasets into a stored procedure (well, its how we did it anyway). If any of my colleagues were to write this today they would probably be despatched home with a copy of Inside SQL Server 2008 : T-SQL Programming tucked under their arm and ordered not to come back until they could recite it ad infinitum but back in 2004, well, we thought we were pretty cool!

Times change, people change and coding practices change too. Code isn't born crap, it just tends to crapness the longer that it lives! Bear that in mind when coding today because in five years time you'll probably be coming back and having a little chuckle at it!

@Jamiet

Published Wednesday, August 10, 2011 5:04 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement