SQL Server Stored Procedure Design for Flexible Record Lookup

When working with data I often run into situations where the available information varies. Sometimes I get a clean patientid, which makes the lookup easy. Other times, all I have is a date of birth and ZIP code or even just an invoice number from billing.

Instead of creating a separate stored procedure for each case or writing messy dynamic SQL, I prefer a cleaner approach. I design one flexible stored procedure that can handle all of these lookup paths while keeping my logic organized which makes the code easier to maintain and helps my system adapt to whatever input it receives.

What This Procedure Supports

• Lookups by @patientid

• Lookups by @dob and @zip

• Lookups by @invoice_no

• Optional parameters with fallback matching

• Clean modular structure using Common Table Expressions (CTEs)

• Output in structured JSON, ideal for API responses

Why Flexible Lookup Matters
I rarely get the same kind of data twice. One part of the system might send me a patientid while another might only have a date of birth and ZIP code or just an invoice number.

Here is how it usually breaks down:

• Scenario: API call with internal ID

• Inputs I Get: @patientid

• Scenario: Registration

• Inputs I Get: @dob, @zip

• Scenario: Billing or payment inquiry

• Inputs I Get: @invoice_no

That kind of variety means I need a lookup approach that can adapt. By building in flexible matching logic, I avoid writing separate stored procedures for each case which helps to keep things consistent, avoid duplication, and help me handle more situations with less code with only one stored procedure instead of multiple.

How It Works
The stored procedure follows a clear order of evaluation:

• Scenario: @patientid provided

• What Happens: Direct match

• Scenario: @dob + @zip provided

• What Happens: Match via demographics

• Scenario: @invoice_no provided

• What Happens: Match via invoice number

• Scenario: Multiple match types provided

• What Happens: Uses first match in priority order

• Scenario: No match

• What Happens: Returns empty JSON

• Scenario: Any error

• What Happens: Returns structured JSON error

Why Use CTEs?
Common Table Expressions (CTEs) help keep the matching logic clean, modular, and easy to extend. Each match strategy goes into its own named block:

• Each strategy is easy to isolate and debug

• Logic is easier to read and debug

• Maintenance is simpler when business rules change

• Match types can be reordered or extended without affecting the others

Stored Procedure Walkthrough
This is a simplified version of the procedure that covers all three match types:

use [thedb]
go

drop procedure if exists GetPatientSumInfo
go

set ansi_nulls on
go

set quoted_identifier on
go

create procedure GetPatientSumInfo
  @patientid int = null,
  @dob date = null,
  @zip varchar(20) = null,
  @invnum varchar(50) = null
as
begin
  set nocount on;

  begin try

    ;with patidmatch as (
      select patientid
      from patinfo
      where patientid = @patientid
    ),
    demographicmatch as (
      select p.patientid
      from patinfo as p
      join patinfo_addr as pa on p.patientid = pa.patientid
      where
        @patientid is null and
        @dob is not null and
        @zip is not null and
        p.dob = @dob and
        pa.zip = @zip
    ),
    invoicematch as (
      select distinct p.patientid
      from orderinfo as o
      join patinfo as p on o.patientid = p.patientid
      where
        @patientid is null and
        @invnum is not null and
        o.invnum = @invnum
    ),
    allmatches as (
      select patientid from patidmatch
      union
      select patientid from demographicmatch
      union
      select patientid from invoicematch
    ),
    matchedpatient as (
      select top 1 patientid
      from allmatches
      order by patientid
    )
    select
      p.patientid,
      p.first_name,
      p.last_name,
      p.dob,
      (
        select
          pa.addr_id,
          pa.street,
          pa.city,
          pa.state,
          pa.zip
        from patinfo_addr as pa
        where pa.patientid = p.patientid
        for json path
      ) as address
    from matchedpatient as m
    join patinfo p on p.patientid = m.patientid
    for json path, without_array_wrapper;

  end try
  begin catch
    select
      error_number() as error_number,
      error_message() as error_message,
      error_line() as error_line,
      error_procedure() as error_procedure
    for json path, without_array_wrapper;
  end catch
end

go

Sample Usage
exec GetPatientSumInfo @dob = '1980-05-01', @zip = '12345'

Sample Output

{
  "patientid": 123,
  "first_name": "John",
  "last_name": "Doe",
  "dob": "1980-05-01",
  "address": [
    {
      "addr_id": 789,
      "street": "123 Main St",
      "city": "Springfield",
      "state": "IL",
      "zip": "12345"
    }
  ]
}

Error Handling
If any unexpected error happens (e.g., bad join, null issue, query failure, etc.), the CATCH block will return a JSON response like this:

{
  "error_number": 208,
  "error_message": "Invalid object name 'patinfo'.",
  "error_line": 7,
  "error_procedure": "GetPatientSumInfo"
}

This is great for logging or for passing through to an API that displays a friendly error message to the caller.

Performance Comparison
I like using the CTEs + UNION approach because it keeps the code clean and easy to maintain, but it is not always the fastest option, especially in systems with a lot of traffic or big datasets.

Depending on the situation, I sometimes consider other approaches. Here is a quick comparison I use when deciding what fits best:

CTEs + UNION

• Performance: Medium

• Maintainability: Excellent

• Best Use: Clean modular logic with de-duplication

• Notes: Extra cost for de-dupe

CTEs + UNION ALL

• Performance: Good

• Maintainability: Excellent

• Best Use: Clean logic when overlap is impossible

• Notes: Slightly faster if safe to use

Single SELECT + OR conditions

• Performance: Fastest

• Maintainability: Moderate

• Best Use: High-performance environments

• Notes: Harder to maintain and scale

Temp table / Table variable

• Performance: Good

• Maintainability: Moderate

• Best Use: Multi-step logic or complex joins

• Notes: Good for control, indexing flexibility

Indexed view

• Performance: Very Fast

• Maintainability: Hard to manage

• Best Use: High-volume repeat queries

• Notes: Overhead on inserts, complex setup

Final Thoughts
This kind of flexible stored procedure has worked really well for me in situations where I need to match records using different types of information. By combining optional parameters, CTEs, and JSON output, I get something that is clean, modern, and easy to connect to an API.

If performance ever becomes a concern, there are some good ways to optimize without throwing out the whole design. I can switch from UNION to UNION ALL if I know the match paths are mutually exclusive. I can also cache commonly used reference data in the application layer, or preload smaller datasets into temp tables inside the procedure. And in heavier systems, I might use indexed views to speed up the matching process by querying a flattened, pre-joined structure.

But for most systems I have worked on, starting with this kind of clean, modular, CTE-based design provides a solid foundation. It is easy to read, easy to extend, and flexible enough to support all kinds of input combinations.