What is better to use for this case, a CTE or a table variable?

Question

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 vs a Table Variable when querying hundreds of thousands of rows and performing calculations per row? 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.

Answer 1

I would probably do it this way, using set-based dynamic SQL instead of any looping constructs, CTEs, etc. We could probably eliminate the table variable as well, but not enough information in the question for that, so this code assumes the table variable is already populated.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
EXEC msdb.dbo.sp_send_dbmail 
  @profile_name = @profileName, 
  @recipients = N''recipientlist'', 
  @subject = N''someSubject'', @body = N''' 
    + col1 + N' and ' 
    + col2 + N' and ' 
    + col3 + N' are the columns'';'
FROM @tableVar1;

EXEC sys.sp_executesql @sql, N'@profileName SYSNAME', @profileName;