SQL Stored Procedure

I have been asked to write a stored procedure that is dynamic for updating/inserting.

The logic that I have been asked to apply does not make sense to me.

Here is what I was asked to write.

Just make the SQL script dynamic so that we can check whether the variable is valid before appending it to the condition or insert values. For example, we don’t want to update the DOB if the value coming in is not a valid date or  null. Also,  user won’t be passing PK as parameter. We need lastname, dob, SSN, Gender as the criteria for checking whether the record exist or not.

To me I see the issue of if I am comparing to see if exists against lastname,dob,ssn,gender then it does not matter what the DOB is I have my stored procedure set to @dob datetime so what it gets it date time.

Any suggesstions or ideas?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would also reject this kind of requests.
if DOB is a criteria to check for the record, but at the same time could be passed with invalid data, it cannot be implemented in the procedure level.

if you "fix" that constraint, or if you can get the PK to be passed to the stored procedure (which should be possible, as normally it should be application CODE calling the procedure, and not the user himself...)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you can implement the procedure.
it would be a MERGE statement (also called UPSERT sometimes):
desiredforsomeAuthor Commented:
I said the same thing. How am I supposed to write a SP and compare variables and then update those variables lol. It would not be able to update because it conflicts with itself.

I wanted to make sure I am not crazy so I can report back to them and say I have checked out my options.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Scott PletcherSenior DBACommented:
Actually, it's not a bad idea for the procedure to do validity and/or reasonable checks on the data coming in, rather than relying 100% on the app.  Developers always want to do all validation in the app, but sometimes new methods of bringing data in get started, so it's good for the db to do a final check.  Who knows what the next new technology for entering data will be.

As to a "reasonable" check, for example, for a current person's DOB, if the date is 9 Feb 1873, that's wrong, even though it's technically valid.

SSN should be checked for digits only.

Maybe verify that name is not "N/A" or a single letter/letter string, such as "x" or "xxx".  Maybe not, it depends on the criticality of the name and the specific app.

Where possible, make use of CHECK constraints, as the rules will then be efficiently enforced by the db itself.
desiredforsomeAuthor Commented:
Yes I understand the checking of validity but the  checking if exists based on specific variables then updating those variables with new ones.
Vitor MontalvãoMSSQL Senior EngineerCommented:
desiredforsome, do you still need help with this question?
desiredforsomeAuthor Commented:
wihout a pk cant be done
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.