Need help to checking data, and update column

Question

Need help to checking data, and update column

Viewing 5 posts - 16 through 19 (of 19 total)

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

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 1

Qira (8/18/2014)
Dear Mr Higgam,
You're helping me sir. Thanks to you. But no thanks to other one.

Thank you for the feedback. I have to say g.britton gave some very valid points to try and understand what you were doing in order to assist you and indeed his suggested solution would indeed work. You must remember that there are always going to be several different solutions to problems and you should not dismiss any of the solutions offered as you may find at some point in the future those solutions stick and help you resolve other issues. Often there is a quicker, better way to do things (I myself have learnt a great deal from these forums). You must remember that everyone on here gives up their own time for free and your comment above does not reflect the true spirit of these forums.:exclamation:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Qira (8/17/2014)
Your criticize is not helping me. So, don't help

Qira (8/17/2014)
You're helping me sir. Thanks to you. But no thanks to other one.

You do realize this is a forum with unpaid volunteers?

You should be grateful to your knees that someone is trying to help you.

If you do not like the free assistance that you get, you're free to hire a consultant.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

gbritton1 (8/18/2014)
higgim (8/18/2014)
gbritton1 (8/18/2014)
...or add the equivalent check constraints and let SQL Server do it for you...
Whilst this is fine and dandy when an insert occurs which fails the checks an error will be returned when trying to insert which will need to be handled via the application and also you would have no visibility afterwards of the attempt as it would never get inserted.
Actually I think its more than that. Why would you want to insert invalid data into a table (and then reject it later)? Why not use check constraints and wrap the INSERT In a TRY..CATCH then do what you want ... including logging the error for later visibility and not returning an error to the application? But considering that the OP already validates the data at the application layer, why do it again in SQL Server?

I wonder if part of the issue is they are not checking and validating data at the application first, and once this passes the test, then doing an insert.

There seems to be something missing in the picture.

For example, if a text box has a dropdown, but can accept freeform text, you can have a problem.

Or nothing is defaulted and nothing was selected, and it should be a mandatory entry, I would not be trying an insert at that point.

Usually when I code something like this, I do a try catch and validate the data in the application form when the submit button is pressed.

Then if it passes, send the insert.

If it fails, I highlight the errors on the form for correction.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I agree with most of what gbritton1 has posted.

This type of business logic does belong in the application and a submit button shouldn't be enabled until all the data passes the tests. This doesn't mean you don't use check constraints on the destination table as well, because, in my experience, there are always cases where someone directly inserts data into the table outside the application. Having the verification in the app and the database is part of "defense in depth" where you don't have one point of verification.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

One of the things a ETL developer will invariably encounter is data that is not trustworthy. And though we can argue that it should be (It should be!), it still a one of those things that we make a determination of, to not trust the data from the source. Maybe the developer is no longer available and a simpler approach is to do validation in the ETL pipeline or in a staging table (it happens).

To carry on with the issue , what about the following solution? (This assuming I understood the business logic correctly.)

UPDATE process_ApplyJob_1

SET checkingStatus = 'True'

WHERE batchId='cb10c302-b591-4c41-84ce-059479ce110e'

AND

(MaritalStatusID = 4

OR

(

( SexID = r_SexID )

AND ( Age BETWEEN r_minAge AND r_maxAge )

AND ( BMI BETWEEN r_minBMI AND r_maxBMI )

AND ( Bumiputera = r_Bumiputera )

AND ( IsParentTNBStaff = r_IsParentTNBStaff )

AND ( MaritalStatusID = r_MaritalStatusID )

AND ( WorkExperience BETWEEN r_minWorkExperience AND r_maxWorkExperience )

)

Does this help you?

----------------------------------------------------