CodeSOD: Inching Along

"Procedures should be as small as possible," is good advice. Like any good advice, you can take it too far.

Mike recently was asked to upgrade a SQL Sever 2000 database to SQL Server 2016. Since this was an upgrade, Mike wasn't supposed to make any changes beyond the necessary changes to make the upgrade work. Still, when he found a bunch of methods with the same basic naming pattern, he had to investigate.

CREATE PROCEDURE [dbo].[sp_inchworm_1wk] ASBEGIN INSERT INTO #inchworm_1wk(") -- seven records get insertedEXEC sp_inchworm_1wk_a1 @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a1]ASBEGININSERT INTO #inchworm_1wk_a1 SELECT * FROM #inchworm_1wk;EXEC sp_inchworm_1wk_a2 @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a2]ASBEGIN INSERT INTO #inchworm_1wk_a2 SELECT * FROM #inchworm_1wk_a1;EXEC sp_inchworm_1wk_a3 @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a3]ASBEGIN INSERT INTO #inchworm_1wk_a3 SELECT * FROM #inchworm_1wk_a2;IF @vendor_flag = 1EXEC sp_inchworm_1wk_a4a @fiscal_wk_end_date,@vendor_flagELSEEXEC sp_inchworm_1wk_a4b @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a4a]ASBEGIN INSERT INTO #inchworm_1wk_a4a SELECT * FROM #inchworm_1wk_a3;EXEC sp_inchworm_1wk_a5a @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a4b]ASBEGIN INSERT INTO #inchworm_1wk_a4b SELECT * FROM #inchworm_1wk_a3;EXEC sp_inchworm_1wk_a5b @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a5a] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT)ASBEGIN INSERT INTO #inchworm_1wk_a5a SELECT * FROM #inchworm_1wk_a4a;EXEC sp_inchworm_1wk_a6 @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a5b] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT)ASBEGIN INSERT INTO #inchworm_1wk_a5b SELECT * FROM #inchworm_1wk_a4b;EXEC sp_inchworm_1wk_a6 @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a6] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT)ASBEGIN INSERT INTO #inchworm_1wk_a6 SELECT * FROM #inchworm_1wk_a5a; INSERT INTO #inchworm_1wk_a6 SELECT * FROM #inchworm_1wk_a5b;IF @vendor_flag = 1EXEC sp_inchworm_1wk_a7a @fiscal_wk_end_date,@vendor_flagELSEEXEC sp_inchworm_1wk_a7b @fiscal_wk_end_date,@vendor_flagENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a7a] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT)ASBEGIN INSERT INTO #inchworm_1wk_a7a SELECT * FROM #inchworm_1wk_a6;EXEC sp_inchworm_1wk_a8ENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a7b] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT)ASBEGIN INSERT INTO #inchworm_1wk_a7b SELECT * FROM #inchworm_1wk_a6;EXEC sp_inchworm_1wk_a8ENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a8]ASBEGIN INSERT INTO #inchworm_1wk_a8 SELECT * FROM #inchworm_1wk_a7a; INSERT INTO #inchworm_1wk_a8 SELECT * FROM #inchworm_1wk_a7b;EXEC sp_inchworm_1wk_a9ENDGOCREATE PROCEDURE [dbo].[sp_inchworm_1wk_a9]ASBEGIN -- returns final data setENDGO

The code worked, in both SQL Server 2000 and 2016, so Mike wasn't allowed to change it. It's on a list of things to fix, someday in the future, as part of paying down technical debt. You could say that they'll keep inching towards fixing this, and maybe they'll get there eventually.

[Advertisement] Forget logs. Next time you're struggling to replicate error, crash and performance issues in your apps - Think Raygun! Installs in minutes. Learn more.