验证存储过程中的数据并在表中插入记录。 [英] Verify data from stored procedure and insert record in a table.

问题描述

我有一些数据表如下表所示我想验证每一行的列数据

在游标内部。

如果列数据在验证表中定义的验证无效，则将这些记录记录到具有相应验证ID的无效数据表中。

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	[e-mail]

2	XYZ	ad	4	8987987978	[e-mail]

3	PQR	sadas	5	8987987978	[e-mail]

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

我尝试过：



更改程序验证数据

（

@ID INT，

@Errormessage VARCHAR（MAX）输出

）



- 写光标

里面的光标



检查列数据是否无效然后返回validationId



将数据插入到无效数据表中，验证ID

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

推荐答案

使用游标效率低下。只需这样做，并将所有无效列放在一行：

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

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

除此之外，正确实现的模式和前端将首先防止无效数据插入数据库，甚至不需要像这样的垃圾。此外，如果您担心数据库中存在无效数据，请在应用程序中处理该可能性并在SQL中进行适当的注释。更好的是，写一些将修复表内容的SQL，并对前端进行更改以防止将来发生这种情况。



最后，如果这是一个家庭作业，这是教导某人关于游标的一种不好的方式，因为这意味着模式设计可以是一个事后的想法，因为，游标！整个思维过程都被破坏了。

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.

这篇关于验证存储过程中的数据并在表中插入记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！