Hello Friends,
I had posted a question earlier, but I don't think I articulated the problem correctly. I have attached 2 images that may help with understanding my problem. I have a stored procedure with lets just say two select statements in this case that are querying tables to see if a ssn exists in either one of the tables, both tables have a ssn column. the ssn is a parameter in this stored proc. the problem I am having is if a ssn does not exist in both of the two tables when I return the results back to my asp.net application where I am using ADO.Net and a data reader, it gives me an exception error. is there a way that if the ssn does not exist in a table I can still return the ssn column with the text no ssn found so that the ADO.Net reader will return the rows. Thanks
Instead of returning the SSN (which is being sent to the procedure already) could you return COUNT(*)? Count always returns a value.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 8, 2020 at 4:24 pm
Another possibility
DECLARE @SSN2 VARCHAR(whatever);
SELECT @SSN2 = SSN
FROM dbo.BaseData bd
WHERE bd.SSN = @SSN;
SELECT SSN = ISNULL(@SSN2, 'No SSN Found');
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 8, 2020 at 4:41 pm
Hi,
You could try (in case you need from a single query) like this:
SELECT IIF(COUNT(1)=0, 'Not Found', MAX(SSN))
FROM
(
SELECT
SSN FROM Table1 WHERE SSN = '401-202004-4000140'
UNION ALL
SELECT
SSN FROM Table2 WHERE SSN = '401-202004-4000140'
) InnerResult
April 12, 2020 at 4:10 pm
IF you are trying to return SSN from table2 only if SSN doesn't exist in table1 and return "No SSN Found" only if it doesn't exist in table1 and table2 then try using SELECT COALESCE(@SSN1,@SSN2,"No SSN Found");
SELECT @SSN1 = SSN FROM Table1 where..
SELECT @SSN2 = SSN FROM Table2 where..
SELECT COALESCE(@SSN1,@SSN2,"No SSN Found") as SSN;
April 12, 2020 at 9:58 pm
Just like the other post, I'll remind you again that you need to tell management to get their act together and stop storing SSNs in clear text. You will never pass a good audit and, if your system is compromised, it may cost the company so much money protecting the individuals that have been violated that it'll drive the company out of business.
And, guess what? It's people like you that will end up paying the price for the company's failure to protect the SSNs and the other PII that you have stored in plain text. It will be a career changing event for you and you won't like your new career.
I'm not saying this to be mean or condescending. It's a real problem for you, the company you work for, and all of your customers. At least encrypt the SSNs with at least a single long salt or better.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply