SENDING THE RESULTS OF A SELECT STATEMENT FROM A #TEMP TABLE WITH Sp_Send_dbMail

• JALLY

  SSCommitted

  Points: 1865

  More actions

  October 18, 2019 at 8:47 pm

  #3690319

  what are the best ways one can send data from a temp table through email using the SQL Email Client: sp_send_dbmail.

  I learn from the footprints of giants......

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  October 19, 2019 at 9:10 pm

  #3690409

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Sumathi

  Valued Member

  Points: 61

  More actions

  October 22, 2019 at 2:08 am

  #3690801

  You can use the below T-SQL:

  DECLARE @email_addr NVARCHAR(450), @min_id int, @max_id int, @query NVARCHAR(1000)

  SELECT @min_id=MIN(id), @max_id=MAX(id) FROM #YourTable

  WHILE @min_id<=@max_id

  BEGIN

  SELECT @email_addr=email_addr FROM #YourTable

  set @query='sp_send_dbmail @profile_name=''ProfileName'',

  @recipients='''+@email_addr+''',

  @subject=''subject line'',

  @body=''Body message.'''

  EXEC @query

  SELECT @min_id=MIN(id) FROM #YourTable where id>@min_id

  END