Stored Procedure

Question

Post reply

Stored Procedure

Data Rat

SSC Eights!

Points: 943
More actions
April 8, 2020 at 6:54 am

#3741305
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 4 years ago by Data Rat .
April 11, 2020 at 11:25 am

This was removed by the editor as SPAM
April 11, 2020 at 12:47 pm

This was removed by the editor as SPAM

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

April 8, 2020 at 9:49 am

#3741348

Have you tried the COALESCE or ISNULL function?

John

Data Rat SSC Eights! Points: 943 More actions · Answer 2

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 More actions · Answer 3

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: 148809 More actions · Answer 4

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: 24675 More actions · Answer 5

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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)

Data Rat SSC Eights! Points: 943 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

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)

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 9

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

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