在msdb.dbo.sp

在msdb.dbo.sp_send_dbmail中使用@country(Use @country in msdb.dbo.sp_send_dbmail)

我有一个程序给我一些数据。 该过程运行4次，@ country改变并提供来自4个不同国家的数据（数据来自不同的数据库。）除了将数据放入文件之外，我还想将每个国家的结果通过电子邮件发送为csv-使用sp_send_dbmail的文件如下所示。

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxx', @recipients = 'xxx', @query = 'SELECT * FROM xxx', @subject = 'Addresses from @country', @attach_query_result_as_file=1, @query_attachment_filename = 'Addresses_@country.csv', @query_result_separator = ';', @query_result_no_padding = 1;

我的问题是：是否可以使用@subject和@query_attachment_filename中的过程中的@country？ 我试过了，但它不起作用。

I have a procedure giving me some data. The procedure is run 4 times with @country changing and giving data from 4 different countries (the data are from differente databases.) In addition to putting the data into a file, I also want to email the result of each country as an csv-file using sp_send_dbmail like showing below.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxx', @recipients = 'xxx', @query = 'SELECT * FROM xxx', @subject = 'Addresses from @country', @attach_query_result_as_file=1, @query_attachment_filename = 'Addresses_@country.csv', @query_result_separator = ';', @query_result_no_padding = 1;

My question is: Is it possible to use @country from the procedure in @subject and @query_attachment_filename? I have tried, but it does not work.

最满意答案

如果我理解正确，你可以使用一个变量：

declare @subject nvarchar(255) = N'Addresses from ' + @country; declare @@query_attachment_filename nvarchar(255) = N'Addresses_@country.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxx', @recipients = 'xxx', @query = 'SELECT * FROM xxx', @subject = @subject, @attach_query_result_as_file=1, @query_attachment_filename = @query_attachment_filename, @query_result_separator = ';', @query_result_no_padding = 1;

If I understand correctly, you can just use a variable:

declare @subject nvarchar(255) = N'Addresses from ' + @country; declare @@query_attachment_filename nvarchar(255) = N'Addresses_@country.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxx', @recipients = 'xxx', @query = 'SELECT * FROM xxx', @subject = @subject, @attach_query_result_as_file=1, @query_attachment_filename = @query_attachment_filename, @query_result_separator = ';', @query_result_no_padding = 1;