Why Reusing Stored Procedures Can Be Problematic: A Practical Example with Job, Certification, and Disclosure Checks

The author recounts a recent project requirement to verify whether a user has Job, Certification, and Disclosure records, prompting a discussion on the suitability of stored procedures for such reusable business logic.

Initially, the existing stored procedure CREATE PROCEDURE [dbo].[GetJobs](@PersonId int, @OrganizaitionId int) AS BEGIN SELECT JobId, JobName, JobType FROM Job WHERE PersonId = @PersonId AND OrganizaitionId = @OrganizaitionId END was called from a new procedure to count the number of jobs returned.

The first solution used a temporary table:

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

While simple, this approach ties the temporary table schema to the output of GetJobs , causing maintenance headaches if the underlying result set changes.

Attempts to replace INSERT INTO … EXEC with SELECT INTO fail because T‑SQL does not support that syntax for stored procedure results, and adding an OUTPUT parameter to GetJobs is risky due to its widespread usage.

The author consulted an MS MVP article on sharing data between stored procedures, noting that T‑SQL is poorly equipped for code reuse compared to languages like C# or Java.

Ultimately, the author duplicated the query logic inside the new procedure, concluding that although stored procedures can offer performance benefits, they are ill‑suited for encapsulating reusable business logic due to poor extensibility, maintainability, and higher risk.