Take care while using SELECT Clause for Variable Assignment - SQL Server

I ran through in one issue while working in one of the stored procedure and using SELECT clause while variable assignment. And I suffered this issue in a live production environment and due to this it was killing the Server CPU.

Finally, I got the issue and resolved it. The issue was It did not take care of variable assignment with SELECT clause. In my earlier post I have written about SET v's SELECT for variable assignment and I discussed the same issue with overview but not given details here. I am going to complete the details and share the issue with you with examples now.

CREATE TABLE FromTab
  (
     Id    INT
     ,Name VARCHAR(50)
  )

GO

CREATE TABLE ToTab
  (
     Id    INT
     ,Name VARCHAR(50)
  )

GO

-- Inserting demo records 
INSERT FromTab
SELECT
  1  ,'First'
UNION ALL
SELECT
  2  ,'Second'
UNION ALL
SELECT
  3  ,'Third'
UNION ALL
SELECT
  4  ,'Fourth'
UNION ALL
SELECT
  5  ,'Fifth'

GO

As discussed we are using SELECT clause for variable assignment here in below script.

DECLARE @Var INT
DECLARE @Startdate DATETIME

SET @Startdate = Getdate() 

SELECT TOP 1
  @Var = Id
FROM   FromTab

WHILE EXISTS(SELECT
               1
             FROM   FromTab
             WHERE  Id <= @Var)
  BEGIN
 
 PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds'
    
      INSERT INTO ToTab
      SELECT
        Id
        ,Name
      FROM   FromTab
      WHERE  Id = @Var

      SELECT TOP 1
        @Var = Id
      FROM   FromTab
      WHERE  Id > @Var
  END

GO

Now toward the solution I am going to use SET for variable assignment in below revised script.

-- First truncate table before go ahead with next
TRUNCATE TABLE ToTab

DECLARE @Var INT
DECLARE @Startdate DATETIME

SET @Startdate = Getdate() 


SELECT TOP 1
  @Var = Id
FROM   FromTab

WHILE EXISTS(SELECT
               1
             FROM   FromTab
             WHERE  Id <= @Var)
  BEGIN
  
   PRINT CONVERT(VARCHAR,DATEDIFF(SECOND,@Startdate,GETDATE())) + ' Seconds'
   
      INSERT INTO ToTab
      SELECT
        Id
        ,Name
      FROM   FromTab
      WHERE  Id = @Var

-- Comment start

      --SELECT TOP 1
      --  @Var = Id
      --FROM   FromTab
      --WHERE  Id > @Var
      
-- Comment end

-- Added code and used SET
      
      SET @Var = (SELECT TOP 1
                    Id
                  FROM   FromTab
                  WHERE  Id > @Var)
                  
-- End Adding
                  
  END

GO 

This is just what I want to share. What you want to comment here?