When Stored Procedures Turn Counterproductive: A Real‑World SQL Reuse Dilemma

The author explains that a new requirement to verify whether a user has Job, Certification, and Disclosure records sparked a debate about the proper use of stored procedures. Although existing C# code already called three procedures to fetch full data, a reviewer argued that only a boolean result was needed, suggesting the logic be moved into the database to return true or false and reduce network traffic.

"I think stored procedures are useful, why don’t you recommend using them?" – a colleague’s question that set the stage for the discussion.

To meet the requirement, the author created a new procedure that called the existing GetJobs procedure, stored its result in 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

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

This approach works but introduces a serious maintenance risk: if the schema of GetJobs changes, the temporary table definition must be updated accordingly. Alternatives such as using SELECT INTO are not supported for this scenario, and adding output parameters to the existing procedure would be risky because many callers depend on its current signature.

"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." – an MS MVP on sharing data between stored procedures.

Ultimately, the author could not find a satisfactory reuse method and resorted to duplicating the query inside the new procedure. The conclusion emphasizes that while stored procedures can offer performance benefits, they are ill‑suited for encapsulating business logic that requires flexibility, extensibility, and easy reuse.