Code Review

I’d like to start a new series that will help you learn some of the techniques I’ve used in the past.  Some of these techniques are useful, some aren’t.  Using SQL is a constant learning process.  You have to be willing to look at your solutions and throw out those ideas that no longer work as well as they used to.  You have to look at other programmers works and find things that work better than your own.  By sharing my old code, I hope to find ideas that still work well, and look for things that don’t.

During this process I hope you can learn from my experience and my mistakes.  Let’s start with some code from March 14, 2007.  This code would have run on a SQL 2005 server.  I realize you will not have access to the data these samples were built on.  But I’ll do my best to explain the code you see below.  This procedure  was written as a check in an ETL process.

 @tablename AS VARCHAR(50),
 @fFilename AS VARCHAR(50)
DECLARE @msgbody AS VARCHAR(8000)

/*Testing Only
DECLARE @tablename AS VARCHAR(50), @filename AS VARCHAR(50)
set @filename = 'GCOADD_354_00025.CSA'
set @tableName = 'tmpaddfile'

IF (SELECT COUNT(*) FROM md.dbo.sysobjects WHERE name = 'tmpACMEOutputErrorMsg') >0
DROP TABLE md.dbo.tmpACMEOutputErrorMsg

CREATE TABLE [md].[dbo].[tmpACMEOutputErrorMsg] (
 [Listnum] [char] (10) NULL,
 [ErrorMsg] [char] (200) NULL

--dynamically generated error check
SET @lStrSQL = 'INSERT INTO md.dbo.tmpACMEOutputErrorMsg SELECT vam.listnum, ''MBID matches both Primary and Secondary Key'''
  + '  as ErrorMsg FROM ' + @tablename
  + ' tmp (NOLOCK) LEFT JOIN v_acmembid vam (NOLOCK) ON LTRIM(RTRIM(tmp.matchback_id)) = LTRIM(RTRIM(vam.primary_MBID)) '
  + ' LEFT JOIN v_acmembid vam2 (NOLOCK) ON LTRIM(RTRIM(tmp.matchback_id)) = LTRIM(RTRIM(vam2.secondary_MBID)) '
  + ' WHERE vam.listnum IS NOT NULL AND vam2.listnum IS NOT NULL'

--print @lStrSQL

IF (SELECT COUNT(*) FROM md.dbo.tmpACMEOutputErrorMsg ) > 0

 SET @msgbody = 'Errors in ADD File ' + @pStrFileName

 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'Automation',
 @recipients = '[email protected]',
 @copy_recipients = '[email protected]',
 @body = @msgbody,
 @query = 'select * from md.dbo.tmpACMEOutputErrorMsg order by ErrorMsg',
 @subject = 'acme ADD File Check';

The first thing I notice when I look at this code is the first parameter,  @tablename AS VARCHAR(50).  When I look down at the dynamically generated error check I recognize right away that this was how I was getting around the fact that SQL 2005 didn’t allow you to pass a table as a parameter.  As of SQL 2008, you can do this.  I would call this function right after loading the raw data into @tableName.  I could generate any number of checks, based on business logic I learn through working with business users.

Also notice the DROP and CREATE TABLE statements for md.dbo.tmpACMEOutputErrorMsg.  I’m basically creating a table that will hold all the errors I detect for in the dynamically generated statements.  If there are any errors generated (COUNT(*) > 0), then I will send an email to myself and an alert group.  This email will contain the records from the tmpACMEOutputErrorMsg table.

Pretty simple stuff.  The big thing I would change now is I would pass the table to the procedure, rather than a reference to the table.  This would change all my dynamic code to static code, and would make this procedure far easier to maintain.  Do you have any questions about this code?  Any suggestions for making it better?  Let me know!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *