SQL Server EXEC "sp_executesql" issues - the procedure returns value but the "select Output variable " statement returns a null value

Question

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.

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?

Answer 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

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')
);