Daniel recently found this pair of stored procedures. While the code within them is simple, they hint at horrors just beyond the edge of the stage, the kinds of things that might drive one mad.

CREATE PROCEDURE [dbo].[sp_SomeProc]
@ID       VARCHAR(6)

AS

IF @ID = '109369'
        BEGIN
                DELETE FROM table1
                WHERE ID = '109369'
        END
IF @ID = '100976'
        BEGIN
                DELETE FROM table1
                WHERE ID = '100976'
        END
GO

CREATE PROCEDURE [dbo].[sp_SomeOtherProc]
@ID       VARCHAR(6)

AS

IF @ID = '109369'
        BEGIN
                DELETE FROM table2
                WHERE ID = '109369'
        END
IF @ID = '100976'
        BEGIN
                DELETE FROM table2
                WHERE ID = '100976'
        END
GO

ID, in both of these tables, is a primary key. Thus, we have two procedures which have the capacity to delete two and exactly two records. If you call them with the correct IDs, they delete the records. IF you call them with any other IDs, they do nothing.

These procedures get called. They get called regularly. Which leaves us with the important question: Why are they doing any of this?

Daniel has no answers for us. But clearly, these two records are important, and they get recreated… somehow. Perhaps by some bulk import process? Or by user action? We're allowed to delete them, but we're not required to. The two tables clearly use the same keys, but there isn't a foreign key constraint between them. Presumably we can also delete other rows in these tables, but not by using these procedures.

At best, these procedures are vestigial, and can be safely removed. At worst… they're doing something important.

[Advertisement] Continuously monitor your servers for configuration changes, and report when there's configuration drift. Get started with Otter today!