How to iterator table records one by one through T-SQL

As we known that SQL is primarily a set-orientated language. Eventually we might implement some validation logic over specific column(s) inside each rows. Performing For-Each like syntax toward row(s) would be topic of this story.
Suppose we have below table schema and records(Member):
ID Age Sex
---- ------ -----
1 20 Male
2 -5 Female
3 36 Female
4 7 Female
5 87 Male
6 222 Male
As the data showed that we got dirty or irrational records (ID №2 and №6) that existed inside our table. We would like to write T-SQL script to capture and detect those data in the early stage before we proceed them into application(s).
The ideal for the case, we could first create a SQL function that handle validation for us. Then apply the validation function row by row to inspect the record(s) whom violated the rule(s).
-- fn_validate_age.sql
CREATE FUNCTION [dbo].[fn_validate_age]
(
@age INT
)
RETURNS BIT
AS
BEGIN
IF @age < 0 OR @age > 150
BEGIN
-- age should valid in rage: 0 ~ 150 (rule)
RETURN 0
END
RETURN 1
END
For manipulate data row by row, the intuition ways would be used “Cursor”.
------------------------------------------------
-- prepare date with dirty existed
------------------------------------------------
DECLARE @Member TABLE
(
[ID] INT
, [Age] INT
, [SEX] VARCHAR(30)
)
INSERT INTO @Member
VALUES
(1, 20, 'Male')
, (2, -5, 'Female')
, (3, 36, 'Female')
, (4, 7, 'Female')
, (5, 87, 'Male')
, (6, 222, 'Male')----------------------------------------------
-- validation.sql
----------------------------------------------DECLARE @ValidationResult TABLE
(
[ID] INT
, [AGE] INT
, [SEX] VARCHAR(30)
, [REASON_NOT_PASSED] VARCHAR(300)
)
DECLARE @ID INT
DECLARE @Age INT
DECLARE @Sex VARCHAR(30)
DECLARE CUR CURSOR FOR
SELECT
[ID]
, [Age]
, [Sex]
FROM @Member
OPEN CUR
FETCH NEXT FROM CUR INTO @ID, @Age , @Sex
WHILE @@FETCH_STATUS = 0
BEGIN
-- valildation logic here
DECLARE @IsValidate BIT = 1
SELECT @IsValidate = [dbo].[fn_validate_age](@Age)
IF @IsValidate = 0
BEGIN
INSERT INTO @ValidationResult
SELECT
@ID AS [ID]
, @Age AS [Age]
, @Sex AS [Sex]
, 'Age value invalid.' AS [REASON_NOT_PASSED]
ENDFETCH NEXT FROM CUR INTO @ID, @Age , @Sex
END-- perform operating over those dirty data....
SELECT *
FROM @ValidationResultCLOSE CUR
DEALLOCATE CUR
Validation result return as below:
ID AGE SEX REASON_NOT_PASSED
2 -5 Female Age value invalid.
6 222 Male Age value invalid.
Somebody might concern about the performance of using “Cursor”. So the alternative way we could did a little trick through recursive CTE.
-----
-- validation.sql (Recursive CTE version)
-----
DECLARE @RowCnt INTSELECT @RowCnt = COUNT(1) FROM @Member;WITH CTE AS
(
SELECT 1 AS i, NULL AS [ID], CAST('' AS VARCHAR(30)) AS [REASON_NOT_PASSED]
UNION ALL
SELECT i+1 AS i
, M.ID AS [ID]
, CASE WHEN [dbo].[fn_validate_age](M.AGE) = 0 THEN 'Age value invalid'
ELSE CAST(NULL AS VARCHAR(30)) END AS [REASON_NOT_PASSED]
FROM CTE
INNER JOIN @Member AS M
ON i = M.ID
WHERE i < @RowCnt + 1
)SELECT M.*
, C.REASON_NOT_PASSED
FROM @Member AS M
INNER JOIN CTE AS C
ON C.ID = M.ID
AND C.REASON_NOT_PASSED IS NOT NULL