Post reply

stored procedure return values

  • April 8, 2020 at 4:06 pm

    Go to Answer

    #3741481

    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

    Attachments:
    You must be logged in to view attached files.

  • April 8, 2020 at 4:21 pm

    Answer

    #3741487

    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

    #3741488

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • April 8, 2020 at 4:41 pm

    #3741495

    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

    #3742296

    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

    #3742316

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply