Verify data from stored procedure and insert record in a table.

Question

0.00/5 (No votes)

See more:

I have some data table as below table an I want to validate each row’s column data
Inside a cursor .
If column data is not valid as validation defined in Validation table then log those record into Invalid Data Table with corresponding validation id.

Data Table	 	 	 	 	 
					
Id	col1	Col2	Col3	Col4	Col5
1	ABC	NULL	2	null	email@gmail.com

2	XYZ	ad	4	8987987978	email@gmail.com

3	PQR	sadas	5	8987987978	email@gmail.com

4	ASD	ccd	0	12212	sasasdsad

Validation Table	 
ValidationId	ValidationDesc
1	col1 cannot be null or cannot be greater than 20 Character.
2	col2 cannot be null or cannot be greater than 50 Character.
3	col3 should be greater than 0.
4	col4  cannot be empty or cannot be greater than 10 character.
5	col5 cannot be null and greater than 50 character.

If Any Column data id invalid then insert those data into below table with corresponding validation id.

Invalid Data Table 		
Id 	DataTableId	ValidationId
1	1	2
2	1	4

What I have tried:

ALTER PROCEDURE Validate Data
(
@ID INT,
@Errormessage VARCHAR(MAX) OUTPUT
)

-- Write Cursor
inside cursor

check column data if it is not valid then return validationId

insert data into invalid data table with validation id

Posted 18-Jun-18 0:40am

suneel kumar gupta

Updated 18-Jun-18 1:53am

Add a Solution

Comments

W∴ Balboos, GHB 18-Jun-18 7:38am

That's quite a chain of events you wish the store procedure to implement. From your "What I have tried:", the answer is apparently - nothing.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2018-06-18T01:54:00

Using a cursor is inefficient. Just do it this way and get all invalid columns in one row:

SQL

INSERT INTO InvalidValidDataTable
(
    id,
    Col1IsValid,
    Col2IsValid,
    Col3IsValid,
    Col4IsValid,
    Col5IsValid
)
SELECT
       CASE WHEN col1 IS NULL OR LEN(col1) > 10 THEN 0 ELSE 1 END as Col1IsValid,
       CASE WHEN col2 IS NULL OR LEN(col2) > 50 THEN 0 ELSE 1 END as Col2IsValid,
       CASE WHEN col3 IS NULL OR col3 <= 0      THEN 0 ELSE 1 END as Col3IsValid,
       CASE WHEN col4 IS NULL OR LEN(col4) < 1 OR LEN(col4) > 10 THEN 0 ELSE 1 END as Col4IsValid,
       CASE WHEN col5 IS NULL OR LEN(col5) > 50 THEN 0 ELSE 1 END as Col5IsValid
FROM dbo.mytable

Beyond that, a properly implemented schema and front end will prevent invalid data from being inserted in the database in the first place, and crap like this wouldn't even be necessary. Furthermore, if you're concerned about the presence of invalid data being in the database, handle the possibility in the application and make appropriate comments in the SQL. Even better, write some SQL that will repair the table contents, and make changes to the front end to prevent this from happening in the future.

Lastly, if this is a homework assignment, it's a poor way to teach someone about cursors, because it implies that schema design can be an afterthought because, well, cursors! The whole thought process is damaged.