代码之家 › 专栏 › 技术社区 › Jessica Warren

为电子邮件创建html代码时使用T-sql循环。如何在这个循环中动态更改表名和变量名?

dynamic-sql html-email stored-procedures tsql sql-server

Jessica Warren · 技术社区 · 5 年前

我还需要每个表上方的标题。

我的问题是在循环中创建xml代码,同时更改表名和变量。

这些表都是从sql中的不同表创建的,让我们调用这个表 "dbo.附加信息“因为有多个摘要,所以有多个表所以有dbo.附加信息1dbo.附加信息2等等。

对于这个例子,假设我有3个表和3个变量。

dbo.additional_info1
dbo.additional_info2
dbo.additional_info3
@var1
@var2
@var3

以下是我构建的代码:

DECLARE @SP tinyint  -- start point 
DECLARE @EP tinyint  -- end point 
DECLARE @body NVARCHAR(MAX)
DECLARE @xml2 NVARCHAR(MAX)
declare @var1 varchar(10)
declare @var2 nvarchar(10)  
declare @var3 nvarchar(10)  


set @var1 = 'James'; 
set @var2  = 'Jess'; 
set @var3 = 'Henry'
set @EP = 3
set @SP = 1

SET @body = '
<table border = 1> 
<tr>
<th> Last Name </th> </tr>
'



set @xml2 = ''

while (@SP < @EP) 
begin 


 set @xml2 =  @xml2 + 
(case when @var1 in  (select name from dbo.names)   
then  '<H3>Info' + convert(varchar,@SP) + (select Location from dbo.names where name = @var1)+ ' Summary</H3>' 
else '<H3>Info' + convert(varchar,@SP) + @var1 + 'Not available </H3>'  end) +
 @body +
 Convert(nvarchar(max), (SELECT
                 [details] as [td], ''

          FROM  dbo.Additional_info1 --- then 2 then 3 
          Order by  [Age]
       For XML path('tr'), Type))
    +'</table>' 
set @sp+=1;


end 

print(@xml2)

上面的代码可以工作,但我希望tablename和@var在@Sp每次递增时都进行调整。我认为代码应该如下所示: 我已经尝试了下面的代码加上100个不同的东西,没有骰子。



 set @xml2 =  @xml2 + 
(case when  concat(@var,  convert(varchar,@SP)) in  (select name from dbo.names)   
then  '<H3>Info' + convert(varchar,@SP) + (select Location from dbo.names where name =  concat((@var or '@var'?),  convert(varchar,@SP)))+ ' Summary</H3>' 
else '<H3>Info' + convert(varchar,@SP) + @var1 + 'Not available </H3>'  end) +
 @body +
 Convert(nvarchar(max), (SELECT
                 [details] as [td], ''

            FROM  concat(dbo.additional_info,  convert(varchar,@SP))

          Order by  [Age]
       For XML path('tr'), Type))
    +'</table>' 
set @sp+=1;


end 

print(@xml2) 

I want the email to look like this: 

Info 1:  Location: America -- this is for @var1 
Table with a few rows of "details."

0 回复 | 直到 5 年前

Robert Hollon 5 年前

澄清一下,在你的岗位上

从dbo.附加信息1---然后是2然后是3

这是你想成为动态的部分,从表1,然后是表2,然后是表3? 请注意,我没有构建要重新创建的表,但应该很接近。如果不行就告诉我!诀窍是,您必须为每个要调用到变量中的内容构建全文字符串,然后像调用函数一样调用该变量。测试这些时,在尝试执行(exec(@DynamicSqlHere);)之前先打印变量(print(@DynamicSqlHere);)。如果您可以复制和粘贴打印的输出,那么输出会提供您所期望的,那么您就得到了您所需要的。

DECLARE @SP tinyint  -- start point 
DECLARE @EP tinyint  -- end point 
DECLARE @body NVARCHAR(MAX)
DECLARE @xml2 NVARCHAR(MAX)
declare @var1 varchar(10)
declare @var2 nvarchar(10)  
declare @var3 nvarchar(10)  


set @var1 = 'James'; 
set @var2  = 'Jess'; 
set @var3 = 'Henry'
set @EP = 3
set @SP = 1

SET @body = '
<table border = 1> 
<tr>
<th> Last Name </th> </tr>
'


set @xml2 = ''


while (@SP < @EP) 
begin 

declare @Location nvarchar(200) = (select Location from dbo.names where name = @var1)
declare @DynamicTableCallHere nvarchar(2000) = ''
declare @Message nvarchar(max) =    (case   
                                    when @Location is not null then '<H3>Info' + convert(varchar,@SP) + @Location + ' Summary</H3>' 
                                    when @Location is null then '<H3>Info' + convert(varchar,@SP) + @var1 + 'Not available </H3>' 
                                    end
                                    )

declare @DynamicSqlHere nvarchar(max) = 'set @DynamicTableCallHere = (Convert(nvarchar(max), (SELECT [details] as [td], '''' FROM  dbo.Additional_info' + @sp + ' Order by  [Age] For XML path(''tr''), Type)))'
exec (@DynamicSqlHere);

set @xml2 = @xml2 +
+ @Message
+ @body
+ @DynamicTableCallHere
+ '</table>';

set @sp+=1;


end 

print(@xml2)

John Cappelletti 5 年前

SQLServer不支持宏替换,因此会留下动态SQL或一些黑客行为,如下所示。

@GetTable

例子

...
FROM ( Select * From dbo.Additional_info1 Where @GetTable=1
       Union All
       Select * From dbo.Additional_info2 Where @GetTable=2
       Union All
       Select * From dbo.Additional_info3 Where @GetTable=3
     ) SrcData
...