Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оформление результата запроса в виде таблицы при отправке письма  [new]
assmsk
Member

Откуда:
Сообщений: 169
Добрый день!
Как сделать чтобы при отправке письма через sp_send_dbmail
результат запроса из переменной @query в теле письма был оформлен в виде таблицы с рамками?




Сейчас письмо имеет такой вид:

Точка
Статус
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
aa89
1
aa90
3
aa88
3
aa23
7


Нужно чтобы в теле письма отображалось:

Точка Статус
aa89 1
aa90 3
aa88 3
aa23 7

+ рамки
7 май 18, 12:27    [21392686]     Ответить | Цитировать Сообщить модератору
 Re: Оформление результата запроса в виде таблицы при отправке письма  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
assmsk,

гугл знает очень много
https://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/
7 май 18, 12:30    [21392703]     Ответить | Цитировать Сообщить модератору
 Re: Оформление результата запроса в виде таблицы при отправке письма  [new]
aleksrov
Member

Откуда:
Сообщений: 948
assmsk,

Так и сделайте из переменной таблицу, а не строку (как видимо сейчас) и все будет нормально.
7 май 18, 12:32    [21392706]     Ответить | Цитировать Сообщить модератору
 Re: Оформление результата запроса в виде таблицы при отправке письма  [new]
court
Member

Откуда:
Сообщений: 1956
declare @query nvarchar(1000)='select top 20 * from master..spt_values'
declare @xml xml
declare @HTML nvarchar(max) 
declare @StartTime datetime=getdate()

--
set @query=N'set @xml=('+@query+N' for xml raw, elements xsinil, type, xmlschema)'

exec sp_executesql @query, N'@xml xml out', @xml=@xml out 

set @HTML =
		N'<html>' +
		N'<head>'+
		N'  <style type="text/css">'+
		N'		th {background-color: #03A2B0;}'+
		N'		table {border-collapse: collapse; padding: 5px;}'+
		N'		table, th, td {border: 1px solid black;}'+
		N'		.tr_class0 {background-color: #CDF3F7;}'+
		N'  </style>'+
		N'</head>'+
		N'<body>'	+
		N'<h3>Прувэт !!1</h3>'+
		N'<table border="1">'+[dbo].[fnRawXMLToHTML](@xml,null)+N'</table>'+
		N'< br /><font size="2" color="#778899" face="Arial"><i>Покеда !</i></font>' +
		N'< br />' +
		N'<font size="1" color="#778899" face="Arial"><i>Время формирования: '+cast(datediff(ms, @StartTime, getdate()) as varchar)+' ms</i></font>' +
		N'</body>' +
		N'</html>';

--print @HTML

exec msdb.dbo.sp_send_dbmail 
			@profile_name='My profile name'
			,@recipients='billy@microsoft.com'
			,@subject='My subject'
			,@body=@HTML
			,@body_format='HTML'


<br />в выделенных строках поправить нужно, а то форум их "съедает" если правильно написать ...

+ fnRawXMLToHTML
create FUNCTION [dbo].[fnRawXMLToHTML](
						 @xml			xml					--	xml-данные в формате raw
						,@th_bgcolor	varchar(20)=null	--	Цвет строки с подписями
)
RETURNS nvarchar(max)
AS
BEGIN
	declare @html nvarchar(max)
	declare @th xml, @td xml
	declare @MoneyFormat nvarchar(20)='###'+char(160)+'###'+char(160)+'##0.00'

	declare @xmlschema table (FieldName nvarchar(100), FieldType nvarchar(100))

	--	Если задана схема
	insert into @xmlschema 
		(FieldName, FieldType)
	select 
		x.z.value('@name', 'varchar(100)') as q 
		,isnull(x.z.value('@type', 'nvarchar(100)'),x1.z1.value('@base', 'nvarchar(100)')) as q1 
	from 
	@xml.nodes('*/*/*/*/*:element') as x(z) 
	outer apply x.z.nodes('*:simpleType/*:restriction') as x1(z1)

	--	Подписи
	set @th=
			(select 
				@th_bgcolor as 'tr/@bgcolor' 
				,(select replace(t.c.value('local-name(.)', 'nvarchar(100)'),'_',' ')  
				  from @xml.nodes('/*:row[1]/*') as t(c) 
				  where t.c.value('local-name(.)', 'nvarchar(100)')<>'trclass'
				  for xml path('th'), type) as tr 
			for xml path(''))

	--	Данные
	set @td=
			(select 	

				case when a.OneRow.value('./*:trclass[1]/text()[1]', 'int') is null 
					then case when row_number()over(order by (select 1)) % 2=1 then 'tr_class1' else 'tr_class0' end
					else 'tr_class'+cast(a.OneRow.value('./*:trclass[1]/text()[1]', 'int') as varchar)
				end   as 'tr/@class'

				,(select 
						  case 
								when a.FieldType in ('sqltypes:int', 'sqltypes:numeric', 'sqltypes:smallint', 'sqltypes:tinyint', 'sqltypes:money') then 'right'
								when a.FieldType in ('sqltypes:datetime') then 'center'
								else null 
						  end	as '@align'
						, 
						  case 
								when a.FieldType in ('sqltypes:money')						
									then Format(t1.c.value('./text()[1]', 'money'), @MoneyFormat) 
								when a.FieldType in ('sqltypes:datetime')
									then Format(t1.c.value('./text()[1]', 'datetime'), 'dd\.MM\.yyyy') 
								else t1.c.value('./text()[1]', 'nvarchar(1000)')  
						  end
						  	
				from a.OneRow.nodes('/*') as t1(c) 

				outer apply (
								select 
									x.FieldType
								from	@xmlschema x 	
								where  x.FieldName=t1.c.value('local-name(.)', 'nvarchar(100)') 
							) a

				where t1.c.value('local-name(.)', 'nvarchar(100)')<>'trclass'

				for xml path('td'), elements, type) as tr

			from
				(select
					 t.c.query('./*') as OneRow
				from @xml.nodes('/*:row') as t(c) ) a
				
			for xml path(''))

	--	"Склейка" результату (char(10) ==> < br />)
	set @html = isnull(cast(@th as nvarchar(max)), '')+isnull(replace(cast(@td as nvarchar(max)),char(10),'< br />'), '')

	--
    return @html   
END


К сообщению приложен файл. Размер - 86Kb
7 май 18, 13:33    [21393022]     Ответить | Цитировать Сообщить модератору
 Re: Оформление результата запроса в виде таблицы при отправке письма  [new]
assmsk
Member

Откуда:
Сообщений: 169
Всем большое спасибо
7 май 18, 14:14    [21393167]     Ответить | Цитировать Сообщить модератору
 Re: Оформление результата запроса в виде таблицы при отправке письма  [new]
BSCHECK
Member

Откуда:
Сообщений: 8
Добрый день!
Действительно, классная функция и пояснение.
Подскажите, а что делать с русскими названиями полей, если нужны в отчете и датами?
А то в письме приходят даты в виде: вместо 20180131, к примеру: x00320180131
Погуглил - не нашел, что подраузмевается под кодом : x003 и как с этим бороться.
13 ноя 18, 08:39    [21732544]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить