Table 表的資訊在 SP中透過變數顯現出來

mssql

twyes 2020-03-04 11:02:07 ‧ 938 瀏覽

分享至

先上表 , 這個表是處理過的表 , 排名依據為營益成本比

目的是要把上表變成如下圖,這部分是要透過 MSSQL的 Database Mail 寄出信件
所以在 Store Procedure中可能有一個變數 @Mail_Body (或者其他變數名) ,
@Mail_Body 的內容就如同下表

問題點在於要提取 Table 中的資料
有二個方法

每一個資訊給一個變數排名1就有4個變數
12個排名共48個變數
跑迴圈或其他方法 1筆1筆提取資料,
但最後要組成如下表的方式 , 然後寄出信件
(用變數組成)

簡單講就是寫一個 Store Procedure 把第一個表的資訊
最後變成如下的文字(用變數組成),顯現出來

以下為資料準備


CREATE TABLE A(
	排名 int ,
	年月 nchar(6) ,
	公司代號 nvarchar(10) ,
	營益成本比 decimal(8, 2) ,
	去年同月營益成本比 decimal(8, 2)  ) 

INSERT A 
VALUES (1, N'201912', N'D01', 4.32 , 28.81 )
,(2, N'201912', N'C02', 2.25 , 1.76 )
,(3, N'201912', N'B23', 1.68 , -0.87 )
,(4, N'201912', N'B14', 1.33 , 0.61 )
,(5, N'201912', N'B18', 0.92 , 0.34 )
,(6, N'201912', N'B16', 0.76 , 1.57 )
,(7, N'201912', N'B17', 0.74 , -2.00 )
,(8, N'201912', N'D02', 0.52 , 0.37 )
,(9, N'201912', N'B22', 0.46 , 0.09 )
,(10, N'201912', N'C05', 0.35 , 1.19 )
,(11, N'201912', N'B12', -0.04 , 0.50 )
,(12, N'201912', N'B20', -1.08 , -1.13 )

=========================================

注意: 以下是 rogeryao 大大的回答 , 因為如果在下面回應
Markdown 的SQL 的語法 , 顯現不出來
所以我將解答寫這下面 , 供參考
先貼一張圖 , 這是下面這段語法跑出來的結果

以下開始為語法

CREATE PROCEDURE AutoMail_001
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @TransactionName Nvarchar(Max) = 'AutoMail_001';
begin try
BEGIN TRANSACTION @TransactionName declare @Subject nvarchar(max)
--
DECLARE @bodyMsg nvarchar(max)
--DECLARE @subject nvarchar(max) 
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''
DECLARE @FieldCombStr NVARCHAR(MAX) = N''

SET @Subject = convert(varchar(8),getdate(),112) +' 營益成本比報表'

DECLARE Table_Cursor CURSOR FOR
SELECT
'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() OVER (ORDER BY 排名))%2 =1 THEN '#FFFFFF' ELSE '#00FFFF' END +';">' +
'<td align="right">' + Convert(Varchar,ROW_NUMBER() OVER (ORDER BY 排名)) + '</td>' +
'<td align="right">' + RTRIM(IsNull(排名,''))+ '</td>' +
'<td>' + RTRIM(IsNull(年月,''))+ '</td>' +
'<td>' + RTRIM(IsNull(公司代號,''))+ '</td>' +
'<td align="right">' + RTRIM(營益成本比)+ '</td>' +
'<td align="right">' + RTRIM(去年同月營益成本比)+ '</td>' +
'</tr>'
FROM [dbo].[A]
WHERE 1=1 ;

OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor
INTO @FieldCombStr;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Table=@Table+@FieldCombStr;
FETCH NEXT FROM Table_Cursor
INTO @FieldCombStr;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;

SET @tableHTML =
N'<H4>'+@Subject+'</H4>' +
N'<table border="1" cellpadding="2" cellspacing="0" style="font-size:13px;font-family:arial,helvetica,sans-serif;" >' +
N'<tr bgcolor="#0000FF">'+
N'<font color="#FFFFFF">'+
N'<td>序號</td>'+
N'<td>排名</td>'+
N'<td>年月</td>'+
N'<td>公司代號</td>'+
N'<td>營益成本比</td>'+
N'<td>去年同月營益成本比</td>'+
N'</font>'+
N'</tr>' +
@Table +
N'</table>'+
N'<br>'+
N'<br>'+
N'<H4 ><font color="#FF0000">'+'******'+ N'</font>'+N'</H4>'+
N'<H4>'+'*-*-*'+ N'</H4>';

exec msdb.dbo.sp_send_dbmail
@profile_name='SQLMAIL', --填你的 DataBase Mail 設定的 profile_name
@recipients='xxx@xxx.com', --填你的EMAIL
--@copy_recipients='',
@subject= @Subject,
@body = @tableHTML,
@body_format = 'HTML' ;

COMMIT TRANSACTION @TransactionName
end try
begin catch

ROLLBACK TRANSACTION @TransactionName

declare @ErrMsg nvarchar(max);
set @ErrMsg = @TransactionName + ' : ' + ERROR_MESSAGE() + ' Line : ' + convert(nvarchar(max),Error_number())

insert into YourDBName.[dbo].[AutoMailError]
(Message)
values
(@ErrMsg)

end catch

END

以下為記錄錯誤 table , 執行有錯時,會記錄在此 Table


CREATE TABLE [dbo].[AutoMailError] (
[Id] uniqueidentifier NOT NULL DEFAULT (newid()) ,
[Message] nvarchar(MAX) NULL ,
[OccurrenceDateTime] datetime NULL DEFAULT (getdate())
)


GO


ALTER TABLE [dbo].[AutoMailError] ADD PRIMARY KEY ([Id])
GO