1

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.

6
  • 2
    Just out of curiosity, why do you need to send one email per row? Why not a single email with all the rows?
    – Hannah Vernon
    Oct 19, 2015 at 19:22
  • Because I need to send additional information and it is a sort of grouping by one of the columns, so, for example, there are several transaction numbers or accounts (so email for each transaction/account), some columns are nullable when they shouldn't be, and the whole thing cannot be redesigned now so I need to inform the users on which record has invalid data. Problem is even for just a few checks, it has to scan huge table, and it might return just two or three rows that match the criteria in the WHERE clause.
    – Ravi
    Oct 19, 2015 at 19:31
  • 1
    What does "better" mean? Do you think choosing a CTE or a table variable will have performance differences that are noticeable on top of queueing up @@ROWCOUNT e-mails? Does it matter how long this process takes (e.g. 1 second vs. 3 seconds) since e-mail is asynchronous and delivery is non-instant anyway? Oct 19, 2015 at 19:34
  • I should have been more specific about second question. Imagine the same sort of query but not for an email, say it is being used to calculate things based on several columns in multiple tables. Say there's a table having just transactions, and then other tables that have related info, and I have to perform calculations per transaction. When there are several hundred thousands of rows of transactions, it takes several seconds to a minute.
    – Ravi
    Oct 19, 2015 at 19:38
  • 3
    Then ask that question. And show your calculations, table structure, indexes, etc. The answer is very rarely to use looping, cursors, or recursion. Oct 19, 2015 at 19:40

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.