"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] AS BEGIN INSERT INTO #inchworm_1wk(…) -- seven records get inserted EXEC sp_inchworm_1wk_a1 @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a1] AS BEGIN INSERT INTO #inchworm_1wk_a1 SELECT * FROM #inchworm_1wk; EXEC sp_inchworm_1wk_a2 @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a2] AS BEGIN INSERT INTO #inchworm_1wk_a2 SELECT * FROM #inchworm_1wk_a1; EXEC sp_inchworm_1wk_a3 @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a3] AS BEGIN INSERT INTO #inchworm_1wk_a3 SELECT * FROM #inchworm_1wk_a2; IF @vendor_flag = 1 EXEC sp_inchworm_1wk_a4a @fiscal_wk_end_date,@vendor_flag ELSE EXEC sp_inchworm_1wk_a4b @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a4a] AS BEGIN INSERT INTO #inchworm_1wk_a4a SELECT * FROM #inchworm_1wk_a3; EXEC sp_inchworm_1wk_a5a @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a4b] AS BEGIN INSERT INTO #inchworm_1wk_a4b SELECT * FROM #inchworm_1wk_a3; EXEC sp_inchworm_1wk_a5b @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a5a] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT) AS BEGIN INSERT INTO #inchworm_1wk_a5a SELECT * FROM #inchworm_1wk_a4a; EXEC sp_inchworm_1wk_a6 @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a5b] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT) AS BEGIN INSERT INTO #inchworm_1wk_a5b SELECT * FROM #inchworm_1wk_a4b; EXEC sp_inchworm_1wk_a6 @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a6] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT) AS BEGIN INSERT INTO #inchworm_1wk_a6 SELECT * FROM #inchworm_1wk_a5a; INSERT INTO #inchworm_1wk_a6 SELECT * FROM #inchworm_1wk_a5b; IF @vendor_flag = 1 EXEC sp_inchworm_1wk_a7a @fiscal_wk_end_date,@vendor_flag ELSE EXEC sp_inchworm_1wk_a7b @fiscal_wk_end_date,@vendor_flag END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a7a] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT) AS BEGIN INSERT INTO #inchworm_1wk_a7a SELECT * FROM #inchworm_1wk_a6; EXEC sp_inchworm_1wk_a8 END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a7b] (@fiscal_wk_end_date SMALLDATETIME ,@vendor_flag TINYINT) AS BEGIN INSERT INTO #inchworm_1wk_a7b SELECT * FROM #inchworm_1wk_a6; EXEC sp_inchworm_1wk_a8 END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a8] AS BEGIN INSERT INTO #inchworm_1wk_a8 SELECT * FROM #inchworm_1wk_a7a; INSERT INTO #inchworm_1wk_a8 SELECT * FROM #inchworm_1wk_a7b; EXEC sp_inchworm_1wk_a9 END GO CREATE PROCEDURE [dbo].[sp_inchworm_1wk_a9] AS BEGIN -- returns final data set END GO

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] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!