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
