Stored Procedure

  • Data Rat

    SSC Eights!

    Points: 943

    Hi Friends,

    I have a stored proc with 45 tables that are used to store ssn's (All of the tables have a ssn field) and I need to display a table in an asp.net application with all of the ssn's before the end user deletes that particular ssn that could be in one or many of these tables. My technical specifications are that I have a stored proc with 45 slect statement querying each one of these tables for a particular ssn. I am calling this stored proc from my application passing it a ssn. My problem is that when I return all the tables and if an ssn is not in a table it returns a null value which ado.net doesnt like. My question is, is there a way to write my select statement in the stored proc to where if the ssn column is null I can give it a value so that ado.net wont have any issues working with the data and i can  pull the data into the application and populate the table. Thanks for any help.

    • This topic was modified 1 month, 4 weeks ago by  Data Rat .
  • John Mitchell-245523

    SSC Guru

    Points: 148747

    Have you tried the COALESCE or ISNULL function?

    John

  • Data Rat

    SSC Eights!

    Points: 943

    I have heard of those functions before but if you could steer me in the right direction on how to apply them to my issue. Thanks !!!

  • Data Rat

    SSC Eights!

    Points: 943

    I apologize for mis wording my original post, but the query doesnt return a null value in the ssn column, it only returns the column name with nothing.

  • John Mitchell-245523

    SSC Guru

    Points: 148747

    Could you post the stored procedure definition, please, or at least enough of it to illustrate your problem?

    Thanks

    John

  • Mr. Brian Gale

    SSC-Insane

    Points: 22790

    Blanks are a bit harder to deal with.  One way would be to add a UNION onto the query... something like:

    UNION
    SELECT NULL
    WHERE NOT EXISTS(SELECT 1
    FROM <table>
    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.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Jeff Moden

    SSC Guru

    Points: 996502

    Shifting gears a bit, it sounds to me like the SSNs in these 45 tables are in clear text.  If that's true, please inform management that's freakin' insanely stupid and that a single data breach will cost them enough money to drive them out of business as well as make a whole lot of people go postal.

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

  • Data Rat

    SSC Eights!

    Points: 943

    This is an in-house utility application that we are using.

  • Jeff Moden

    SSC Guru

    Points: 996502

    Understood but many such "in-house-only" application and other data has been compromised both when someone gets into the nickers of your "in-house" domain and from "inside-jobs" done by less than scrupulous people that may be employed at the company.  All it takes is one pissed off employee.   And even though it's "in-house-only", that won't stop auditors from failing your company on the audit because of it.

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17275

    " it returns a null value which ado.net doesnt like"

    Really!, there is a database driver that doesn't handle nulls?!

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

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