Why Using Stored Procedures for Simple Existence Checks Can Be Problematic

In this technical note, a senior architect shares a recent project requirement: verify whether a user has Job, Certification, and Disclosure records without retrieving the full data sets.

I think stored procedures are useful, why don’t you recommend them?

The author initially called three existing stored procedures ( GetJobs , etc.) from C# to fetch the full records, but a code reviewer argued that only a boolean result (has/has‑not) is needed, which would reduce network traffic and clarify the API contract.

To achieve this, the author attempted to wrap the existing procedures in a new stored procedure that counts the rows returned by GetJobs . The first solution used a temporary table to store the result set and then performed COUNT(*) on it:

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 method works but creates a maintenance burden: any change to the output columns of GetJobs would require updating the temporary table definition.

Replacing the INSERT INTO … EXEC with a SELECT INTO is not supported in T‑SQL, so that shortcut is unavailable.

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.

Ultimately, the author could not find a satisfactory way to reuse the existing procedure without modification, so the new procedure simply re‑implemented the query for Jobs directly.

The conclusion is that, although stored procedures can offer performance benefits, they are ill‑suited for encapsulating generic business logic that needs to be reused across applications; client‑side code typically provides better reusability, extensibility, and maintainability.