When Stored Procedures Turn Into a Maintenance Nightmare

The author shares a recent project issue that prompted a deeper look at using stored procedures for checking whether a user has Job, Certification, and Disclosure records. Initially, three existing stored procedures were called from C# to retrieve full data sets, but the new requirement only needed boolean existence checks.

"If each call duplicated the data‑retrieval logic, any change in business rules would require updating every place that reads Job, Certification, or Disclosure," the author notes.

To reduce network traffic and improve interface semantics, the author attempted to move the existence logic into the database, returning only true/false values. The first approach involved creating a new stored procedure that called the existing ones, inserted their result sets into a temporary table, and then counted the rows.

CREATE PROCEDURE [dbo].[GetJobs]
(
    @PersonId int,
    @OrganizaitionId int
)
AS
BEGIN
  SELECT JobId, JobName, JobType
  FROM Job
  WHERE PersonId = @PersonId AND OrganizaitionId = @OrganizaitionId
END

This method works but introduces a maintenance risk: if the original GetJobs procedure changes its output columns, the temporary table definition must also be updated.

CREATE PROCEDURE [dbo].[MyProc]
(
    @PersonId int,
    @OrganizaitionId int,
)
AS
BEGIN
  CREATE TABLE #Temp(
    PersonId int,
    OrganizaitionId int
  )

  INSERT INTO #Temp EXEC dbo.GetJobs
    @PersonId = @PersonId,
    @ParentOrgId = @ParentOrgId

  SELECT COUNT(*) FROM #Temp
END

Attempting to replace the INSERT INTO … EXEC with a SELECT INTO was not possible because T‑SQL does not support that pattern. Modifying the existing GetJobs procedure to add an output parameter was considered too risky due to its widespread usage.

"Keep in mind that compared to languages such as C# and Java, Transact‑SQL is poorly equipped for code reuse; solutions in T‑SQL to reuse code are clumsier," the author quotes an MS MVP.

Ultimately, the author wrote the necessary query directly in the new procedure, acknowledging that while stored procedures can offer performance benefits, they are ill‑suited for encapsulating reusable business logic. The trade‑off between performance and maintainability often favors moving such logic to the application layer.