The Daily WTF

“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

To read the full article click on the 'post' link at the top.