How to send parameters to BCP inside dynamic sql using sp_executesql

Question

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a parameter. Is there any way to send that parameter value all the way in? When running this, the error message is: Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@RId".

Query:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = @RId" queryout @Path -t, -T -N -S SERVERNAME\INSTANCENAME''';

EXEC sp_executesql @SQL,
                   N'@RId nVARCHAR(500), @Path VARCHAR(500)',
                   @REPORTID,
                   @Path;

Answer 1

I think you'll have to externalize those parameters when generating the dynamic SQL. Something like:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';

DECLARE @SQL nvarchar(500);

SET @SQL
    = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''''' + @REPORTID + '" queryout ' + @Path + ' -t, -T -N -S SERVERNAME\INSTANCENAME''';
PRINT @SQL
--EXEC sp_executesql @SQL

---

The PRINT statement reveals the generated dynamic SQL

master.dbo.xp_cmdshell 'BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''SomeID_Resend2019-02-20 11:38:24.040" queryout \\server\folder\_FileName.csv -t, -T -N -S SERVERNAME\INSTANCENAME'