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/

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] 
 END
FETCH NEXT FROM CUR INTO @ID, @Age , @Sex
END
-- perform operating over those dirty data....
SELECT *
FROM @ValidationResult
CLOSE 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 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

• https://stackoverflow.com/questions/18513986/how-to-write-a-foreach-in-sql-server
• https://www.mssqltips.com/sqlservertip/6148/sql-server-loop-through-table-rows-without-cursor/