英文:
SQL Server EXEC "sp_executesql" issues - the procedure returns value but the "select Output variable " statement returns a null value
问题
I understand you'd like a translation of the code and the issue description. Here's the translated code and relevant information:
我有以下代码作为存储过程的一部分，其中我尝试动态发送电子邮件。
我尝试使用@Queryresult作为HTML表格的一部分，添加到电子邮件正文中，但我无法获取@Queryresult的值。
DECLARE @DataBaseName varchar(150) = 'XXXX'
DECLARE @RowList varchar(150) = '1,2,3,4'
DECLARE @MailSubject varchar(250) = CONCAT(@DataBaseName, N' - 执行报告 - ', CURRENT_TIMESTAMP)
DECLARE @QueryResult nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @sSQL NVARCHAR(MAX) = N'SELECT CAST ((SELECT
td = RowId, '',
td = RunId, '',
td = Description, '',
td = ObjectId, '',
td = ObjectName, '',
td = ExecutionType, '',
td = ExecutionTime, '',
td = StartTime, '',
td = EndTime, '',
td = Status, '',
td = StatusDescription, '',
td = CreationUserId
FROM ['+@DataBaseName+'].log.vExecutionQueue WHERE Rowid IN ('+ @RowList +')
FOR XML PATH(''tr''), TYPE
) AS varchar(max) )
'
EXECUTE sp_executesql @sSQL, N'@QueryResult nvarchar(max) output', @QueryResult OUTPUT
SELECT @QueryResult
在通过SSMS运行代码时，“EXECUTE sp_executesql @sSQL, N'@QueryResult nvarchar(max) output', @QueryResult OUTPUT” 语句会返回一个值。
但是对于“select @QueryResult”，会返回Null值，请参见截图。
我之后使用了这个变量：
Set @tableHTML =
N'<H1>计划执行报告 </H1>' +
N'<table border="1">' +
N'<tr>'+
N'<th>RowId</th>' +
N'<th>RunId</th>' +
N'<th>Description</th>' +
N'<th>ObjectId</th>' +
N'<th>ObjectName</th>' +
N'<th>ExecutionType</th>' +
N'<th>ExecutionTime</th>' +
N'<th>StartTime</th>' +
N'<th>EndTime</th>' +
N'<th>Status</th>' +
N'<th>StatusDescription</th>' +
N'<th>CreationUserId</th>' +
N'</tr>' +
@QueryResult +
N'</table>' ;
我看不出问题所在，请帮助，谢谢！
I hope this helps. If you have any further questions or need assistance with any specific part of the code, please let me know.
<details>
<summary>英文:</summary>
I have the following code as part of A stored procedure where I try to send emails dynamically.
I try to use the @Queryresult as part of an HTML table to be added to the email body but I can't get the @Queryresult value.
`DECLARE @DataBaseName varchar(150) = 'XXXX'
DECLARE @RowList varchar(150) = '1,2,3,4'
DECLARE @MailSubject varchar(250) = CONCAT(@DataBaseName , N' - Execution Report - ' , CURRENT_TIMESTAMP)
DECLARE @QueryResult nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @sSQL NVARCHAR(MAX) = N'SELECT CAST ((Select
td = RowId, '''',
td = RunId, '''',
td = Description, '''',
td = ObjectId, '''',
td = ObjectName, '''',
td = ExecutionType, '''',
td = ExecutionTime, '''',
td = StartTime, '''',
td = EndTime, '''',
td = Status, '''',
td = StatusDescription, '''',
td = CreationUserId
from ['+@DataBaseName+'].log.vExecutionQueue Where Rowid in ('+ @RowList +')
FOR XML PATH(''tr''), TYPE
) As varchar(max) )
'
EXECUTE sp_executesql @sSQL, N'@QueryResult nvarchar(max) output', @QueryResult output
select @QueryResult`
When running the code via SSMS, I get a value for the statement "EXECUTE sp_executesql @sSQL, N'@QueryResult nvarchar(max) output', @QueryResult output ".
But Null value for "select @QueryResult", [see screenshot](https://i.stack.imgur.com/jVPa0.png).
I use the variable afterwards
Set @tableHTML =
N'<H1>Scheduled Execution Report </H1>' +
N'<table border="1">' +
N'<tr>'+
N'<th>RowId</th>' +
N'<th>RunId</th>' +
N'<th>Description</th>' +
N'<th>ObjectId</th>' +
N'<th>ObjectName</th>' +
N'<th>ExecutionType</th>' +
N'<th>ExecutionTime</th>' +
N'<th>StartTime</th>' +
N'<th>EndTime</th>' +
N'<th>Status</th>' +
N'<th>StatusDescription</th>' +
N'<th>CreationUserId</th>' +
N'</tr>' +
@QueryResult +
N'</table>' ;
I can't see what the issue is, help, please?
</details>
# 答案1
**得分**: 1
以下是您要翻译的内容：
"You need to assign the XML result to the variable."
"Also:
* Database names should be `sysname`.
* Don't inject data into your query. Use `STRING_SPLIT` if you really have to, or maybe a table valued parameter.
* The `CAST` is not necessary, just remove the `, TYPE` parameter.
* If you need to inject the database name, use `QUOTENAME` to safely inject. However there is a better method using `EXEC @proc`"
```tsql
DECLARE @DataBaseName sysname = 'XXXX';
DECLARE @RowList varchar(max) = '1,2,3,4';
DECLARE @QueryResult nvarchar(max);
DECLARE @tableHTML nvarchar(max);
DECLARE @sSQL NVARCHAR(MAX) = N'
SET @QueryResult = (
Select
td = RowId,
td = RunId,
td = Description,
td = ObjectId,
td = ObjectName,
td = ExecutionType,
td = ExecutionTime,
td = StartTime,
td = EndTime,
td = Status,
td = StatusDescription,
td = CreationUserId
from log.vExecutionQueue
Where Rowid in (SELECT CAST(Id AS int) FROM STRING_SPLIT(@RowList, ','))
FOR XML PATH('tr')
);
';
DECLARE @proc nvarchar(1000) = @DataBaseName + '.sys.sp_executesql';
EXEC @proc @sSQL,
N'@RowList varchar(max), @QueryResult nvarchar(max) output',
@RowList = @RowList,
@QueryResult = @QueryResult output ;
select @QueryResult;
"You may find the XHTML table easier if you unpivot the columns into a td column and create XML from that, all within an unnamed subquery column. For example"
SET @QueryResult = (
Select
(
select td, ''
from (values
(CAST(RowId AS sql_variant)),
(RunId), (Description), (ObjectId), (ObjectName), (ExecutionType),
(ExecutionTime), (StartTime), (EndTime), (Status),
(StatusDescription), (CreationUserId)
) v(td)
FOR XML PATH(''), TYPE
)
from log.vExecutionQueue
FOR XML PATH('tr')
);
英文:
You need to assign the XML result to the variable.
Also:
- Database names should be
sysname. - Don't inject data into your query. Use
STRING_SPLITif you really have to, or maybe a table valued parameter. - The
CASTis not necessary, just remove the, TYPEparameter. - If you need to inject the database name, use
QUOTENAMEto safely inject. However there is a better method usingEXEC @proc
DECLARE @DataBaseName sysname = 'XXXX';
DECLARE @RowList varchar(max) = '1,2,3,4';
DECLARE @QueryResult nvarchar(max);
DECLARE @tableHTML nvarchar(max);
DECLARE @sSQL NVARCHAR(MAX) = N'
SET @QueryResult = (
Select
td = RowId, '''',
td = RunId, '''',
td = Description, '''',
td = ObjectId, '''',
td = ObjectName, '''',
td = ExecutionType, '''',
td = ExecutionTime, '''',
td = StartTime, '''',
td = EndTime, '''',
td = Status, '''',
td = StatusDescription, '''',
td = CreationUserId
from log.vExecutionQueue
Where Rowid in (SELECT CAST(Id AS int) FROM STRING_SPLIT(@RowList, ','))
FOR XML PATH(''tr'')
);
';
DECLARE @proc nvarchar(1000) = @DataBaseName + '.sys.sp_executesql';
EXEC @proc @sSQL,
N'@RowList varchar(max), @QueryResult nvarchar(max) output',
@RowList = @RowList,
@QueryResult = @QueryResult output ;
select @QueryResult;
You may find the XHTML table easier if you unpivot the columns into a td column and create XML from that, all within an unnamed subquery column. For example
SET @QueryResult = (
Select
(
select td, ''
from (values
(CAST(RowId AS sql_variant)),
(RunId), (Description), (ObjectId), (ObjectName), (ExecutionType),
(ExecutionTime), (StartTime), (EndTime), (Status),
(StatusDescription), (CreationUserId)
) v(td)
FOR XML PATH(''), TYPE
)
from log.vExecutionQueue
FOR XML PATH('tr')
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论