Why Reusing Stored Procedures Is Tricky: Counting Results and Maintaining Code

The author reflects on the use of stored procedures, citing the Alibaba Java Development Manual and a colleague's question about why stored procedures are discouraged for certain business logic.

In a recent project the requirement is to verify whether a user has Job, Certification, and Disclosure records; existing C# code calls three stored procedures (e.g., dbo.GetJobs ) to retrieve the full data sets.

To reduce network traffic the reviewer suggests moving the existence check into the database so that only true/false is returned, which leads to the need for a new procedure that counts the number of jobs returned by GetJobs .

Example of the original GetJobs procedure:

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

Initial solution uses a temporary table to capture the result set and then counts the rows:

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 creates a maintenance burden because any change in the result set of GetJobs requires a matching change in the temporary table definition.

Alternative ideas such as SELECT INTO , adding output parameters, or other data‑sharing techniques are either unsupported or risky, as highlighted by a referenced article on sharing data between stored procedures.

The author concludes that, although stored procedures can offer performance advantages, they are ill‑suited for reusable business logic; rewriting the query directly in the new procedure is often the more maintainable solution.