bcp export syntax error on parameter

282 Views Asked by At

I was wondering how to incorporate a parameter in my bcp with stored procedure the error is at line 8 @JDEBatchNumber any idea how to add this parameter? thank you 

DECLARE @cmd varchar(500)
Declare @TimeStamp as nvarchar(100) = Replace((CONVERT(varchar(25), getdate(), 121)),':','')
Declare @JDEBatchNumber as float = '566521'
Declare @NumberOfRecords as bigInt = (select count(*) From [JDE_PRODUCTION].[PRODDTA].[F0911] Where GLICU = @JDEBatchNumber)


if @NumberOfRecords > 0
SET @cmd = 'BCP "EXECUTE [F0902].[D365O].[Get-F0911NewRecordsByBatchNumber] '+@JDEBatchNumber+'" QUERYOUT "D:\D365O\DataSource\F0911\Input\'+@TimeStamp+'.csv" -c -t\^, -T -S' + @@SERVERNAME + ''
EXECUTE MASTER..xp_cmdshell @cmd;
1

There are 1 best solutions below

2
On

You can't concat a float with a + since it tries to add it... just cast it first.

SET @cmd = 'BCP "EXECUTE [F0902].[D365O].[Get-F0911NewRecordsByBatchNumber] '+ cast(@JDEBatchNumber as varchar(64))+'" QUERYOUT "D:\D365O\DataSource\F0911\Input\'+@TimeStamp+'.csv" -c -t\^, -T -S' + @@SERVERNAME + ''

Although, I think this is more what you want...

SET @cmd = 'BCP "EXECUTE [F0902].[D365O].[Get-F0911NewRecordsByBatchNumber] '''+ cast(@JDEBatchNumber as varchar(64))+''' QUERYOUT "D:\D365O\DataSource\F0911\Input\'+@TimeStamp+'.csv" -c -t\^, -T -S' + @@SERVERNAME + ''