Why Using Stored Procedures for Simple Boolean Checks Can Be Problematic

The author recounts a recent project requirement to verify whether a user has Job, Certification, and Disclosure records, and notes that the existing C# code already calls three stored procedures ( GetJobs, etc.) to retrieve the full data sets.

To reduce network traffic and align with interface design principles, the reviewer suggested returning only true/false flags from the database instead of the full result sets, prompting the author to create a new stored procedure that calls the existing ones and counts the rows.

One straightforward solution uses a temporary table to capture the result of dbo.GetJobs and then applies SELECT COUNT(*) FROM #Temp. While simple, this approach tightly couples the temporary table schema to the output of GetJobs, making future changes fragile.

The author also considered SELECT INTO and adding output parameters to the original procedures, but both options are either unsupported or risky because GetJobs is already used in many places.

Quoting an MS MVP, the author highlights that T‑SQL is poorly equipped for code reuse compared with languages like C# or Java, and that most reuse techniques are clumsy.

Ultimately, the author rewrote the query directly in the new procedure, acknowledging that while stored procedures can offer performance benefits, they are not ideal for encapsulating business logic that needs to be reused across applications; client‑side code provides better maintainability and extensibility.

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