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

Photo from: https://learnsql.com/blog/t-sql-vs-standard-sql-whats-the-difference/
ID     Age     Sex
---- ------ -----
1 20 Male
2 -5 Female
3 36 Female
4 7 Female
5 87 Male
6 222 Male
-- 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
------------------------------------------------
-- 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]
END
FETCH NEXT FROM CUR INTO @ID, @Age , @Sex
END
-- perform operating over those dirty data....
SELECT *
FROM @ValidationResult
CLOSE CUR
DEALLOCATE CUR
-----
-- validation.sql (Recursive CTE version)
-----
DECLARE @RowCnt INT
SELECT @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

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store