Why Reusing Stored Procedures Can Be a Pitfall: Lessons from Real Projects

The author explains that the discussion is not meant to be a textbook on stored procedures, but stems from a recent project issue involving data validation for Job, Certification, and Disclosure records.

A colleague asked why stored procedures were not recommended, prompting a deeper look at code reuse and interface design. The existing C# layer calls three stored procedures to fetch full data sets, but a reviewer suggested returning only boolean flags (has Job, has Certification, has Disclosure) to reduce network traffic and improve API semantics.

To meet the new requirement, the author created a new stored procedure

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

which uses a temporary table to count rows returned by the existing

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

. While this works, it creates a maintenance burden because any change to the result set of GetJobs requires updating the temporary table definition.

The author notes that alternatives such as SELECT INTO, adding output parameters, or modifying the original procedure are either unsupported or risky due to existing dependencies.

Referencing an article on sharing data between stored procedures, the author highlights that T‑SQL is poorly equipped for code reuse compared to languages like C# or Java, making reusable solutions clumsy.

Ultimately, the author could not find a satisfactory reuse pattern and resorted to writing the query directly in the new procedure, concluding that although stored procedures can offer performance benefits, embedding generic business logic in them hampers reuse, extensibility, and maintainability, and is often better handled in application code.