I have a problem when putting the query in the body of the email, it is giving me error asking EXISTS in the query how can I solve this.
Follow my code.
DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
SET @p_profile_name = N'Estoque Minimo'
SET @p_recipients = N'[email protected]'
SET @p_subject = N'Teste de envio'
SET @p_body = '<b>Segue em anexo o Relatório de Produtos abaixo doestqoeu mínimo.</b>'
+ '<table border=1 width="100%"><tr><th colspan="8" bgcolor="darkblue">'
+ '<h3>Relatorio de Estoque para Compras </h3></th></tr>' --titulo da tabela
+ '<tr bgcolor="lightblue"><th>Produto<th>Saldo<th><th>Saldo em Compras<th><th><th>Estque Minimo<th></tr>' --colunas do email
+ ( SELECT DISTINCT
SB1.B1_DESC AS PRODUTO,
SUM(SB8.B8_SALDO) AS SALDO,
(SC7.C7_QUANT - SC7.C7_QUJE) AS [SALDO DE COMPRAS],
SC7.C7_NUM AS PEDIDO,
SB1.B1_EMIN AS [SALDO MINIMO EM ESTOQUE]
FROM DADOSADV12.dbo.SB1010 AS SB1
INNER JOIN DADOSADV12.dbo.SB8010 AS SB8 WITH(NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
INNER JOIN DADOSADV12.dbo.SC7010 AS SC7 WITH(NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE SB1.B1_COD = '000070'
AND SB8.B8_LOCAL IN ('01', '95')
AND SC7.C7_ENCER <> 'E'
AND SB1.D_E_L_E_T_ = ''
AND SB8.D_E_L_E_T_ = ''
AND SC7.D_E_L_E_T_ = ''
GROUP BY SB1.B1_DESC, SB1.B1_EMIN, SC7.C7_QUANT, SC7.C7_QUJE, SC7.C7_NUM)
+ '<tr><td colspan="8" bgcolor="darkblue" align="right">'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @p_profile_name,
@recipients = @p_recipients,
@body = @p_body,
@body_format = 'HTML',
@subject = @p_subject
GO