Exporting table parameter result as .txt file using stored procedure

Question

Is there a way that I could use for me to export the table parameter into .txt file in SQL Server?

Sample code that I did for trying to print out the

DECLARE @testTable TABLE (fld_Name VARCHAR(12), fld_Number VARCHAR(11))
DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)
DECLARE @fileExtension varchar(5) = 'txt'

INSERT INTO @testTable
SELECT [fld_Name] AS fld_Name,
        [fld_Number] AS fld_Number
FROM TableBA tblBA
INNER JOIN TableCAS tblCAS ON
tblCAS.fld_Code = tblBA.fld_AccountNumber
WHERE [fld_Name] NOT IN (SELECT [fld_Name] FROM TableLeads)

declare @fn varchar(500) = 'D:/Test/Leads_'+@fileTimeStamp+'.'+@fileExtension;
declare @cmd varchar(8000) = concat('echo ', @testTable, ' > "', @fn, '"');
print @cmd 
exec xp_cmdshell @cmd,  no_output

set @cmd  = concat('type "', @fn, '"');

print @cmd 
exec xp_cmdshell @cmd;

Using that gives me an error of:

"Must declare the scalar variable "@testTable"

Is there a way that I could print the SELECT * results from the table parameter I created?

Answer 1

Try Like this:

    declare @tablename varchar(100)='@testTable'
    DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)
    DECLARE @fileExtension varchar(5) = 'txt'
    
    EXEC ('DECLARE '+@tablename+' TABLE (fld_Name VARCHAR(12), fld_Number VARCHAR(11)) ;
    INSERT INTO '+@tablename+'
    SELECT [fld_Name] AS fld_Name,
            [fld_Number] AS fld_Number
    FROM TableBA tblBA
    INNER JOIN TableCAS tblCAS ON
    tblCAS.fld_Code = tblBA.fld_AccountNumber
    WHERE [fld_Name] NOT IN (SELECT [fld_Name] FROM TableLeads);
    ')
    declare @fn varchar(500) = 'D:/Test/Leads_'+@fileTimeStamp+'.'+@fileExtension;
    declare @cmd varchar(8000) =  concat('echo ', @tablename, ' > "', @fn, '"');
    print @cmd