Blanks are a bit harder to deal with. One way would be to add a UNION onto the query... something like:
WHERE NOT EXISTS(SELECT 1
WHERE SSN = @SSN
You'd need something like that for each of your 45 tables mind you. BUT this way in the event no results are found, you get "NULL" (I think .NET calls this dbNull but it has been a little bit since I dealt with that). You can change the NULL to whatever you wanted (such as 'Not Found') or whatever makes sense.
Pain in the butt doing that for 45 tables, but it ensures you get a result IF the SSN is not found.
Failing that, you could have your .NET app do a row count on each of the 45 tables it gets back and do something with the datatables that have 0 rows.