I sometimes need to write t-SQL code to send emails *per row* based on some conditional checks on some tables. So because the original table does not have an identity or some such column to track rows, I have to insert the data i want into a table variable, something like this:

    DECLARE @i int
    DECLARE @rowCount int
    DECLARE @body nvarchar(max)
    DECLARE @tableVar1 TABLE(rowid int identity(1,1), col1 nvarchar(10), col2 nvarchar(10), col3 nvarchar(30))

    INSERT INTO @tableVar1(col1, col2, col3)
    SELECT col1,col2,col3 
    FROM MyTable
    WHERE (some condition(s))


    SET @rowCount = @@ROWCOUNT

    SET @i = 1

    WHILE (i < @rowCount)
    BEGIN

	SET  @body = (SELECT col1 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col2 from @tableVar1 where rowid = @i) + ' and ' + (SELECT col3 FROM @tableVar1 where rowid = @i) + ' are the columns.'
	EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName, @recipients = 'recipientlist', @subject = 'someSubject', @body = @body

    SET @i = @i + 1
	
    END

Now, I have two questions, first is, when there is a lot of resultant data from the table, it becomes a mess of several dozens of separate SELECTS from the table variables just to retrieve data from say, one column, then more SELECTS from another column, etc. Is there another way for such a scenario to avoid a lot of SELECTs?

Second, would a CTE be better for this sort of a job? I don't fully understand CTEs and have not really used them. For this sort of a thing, I only use the kind of solution I have written above.