stored procedure return values

  • Data Rat

    SSC Eights!

    Points: 943

    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.
  • scdecade

    SSC Eights!

    Points: 807

    Instead of returning the SSN (which is being sent to the procedure already) could you return COUNT(*)?  Count always returns a value.

  • Phil Parkin

    SSC Guru

    Points: 244449

    Another possibility

    DECLARE @SSN2 VARCHAR(whatever);

    SELECT @SSN2 = SSN
    FROM dbo.BaseData bd
    WHERE bd.SSN = @SSN;

    SELECT SSN = ISNULL(@SSN2, 'No SSN Found');

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • debasis.yours

    SSCrazy

    Points: 2926

    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
  • RC

    Grasshopper

    Points: 12

    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;
  • Jeff Moden

    SSC Guru

    Points: 996502

    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)
    Forum FAQ

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

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