SQL Server uses the bcp command to generate a text file from the SQL statement results

In SQL Server, you can call the command line tool bcp under DOS to generate text files for the data or SQL statement results in the table.
BCP command parameter format:
Copy codeThe Code is as follows:
BCP {dbtable | query} {in | out | queryout | format} datafile
[-M maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-B batchsize]
[-N native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-I inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-K keep null values] [-E keep identity values]
[-H "load hints"]

However, the operations below the command line are not flexible in transferring variables. I refer to ZOU Jian's example of writing File2Table in CSDN,
Wrote a system stored procedure for generating text files based on SQL statement results: sp_ SQL _query_to_file. SQL:
Copy codeThe Code is as follows:
/*************************************** ************************************
// Description: generate text files based on SQL statements (with a date stamp, fields are separated by commas)
// The names of all tables in the SQL statement must be marked in detail using [database name]. [user name]. [Table name]
// Create: maggiefengyu mail: maggiefengyu@tom.com Date:
**************************************** ***********************************/
Create PROCEDURE sp_ SQL _query_to_file (
@ Servername varchar (50), -- server name
@ Username varchar (50), -- user name. If you use the NT authentication method, it is null''
@ Password varchar (50), -- password
@ SQL _query varchar (2000), -- SQL query statement. Use single quotation marks when character conditions exist.
@ FilePath_name varchar (1000), -- export path and file name
@ Date datetime -- the generated date stamp when saving the file
)
DECLARE @ SQL varchar (8000)
SET @ SQL = 'bcp "'+ @ SQL _query +'" queryout "+ '"' + @ filePath_name + '_' +
Convert(varchar(12,,@date,{}'.txt "/t", "/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username END
+ '/P' + ISNULL (@ password ,'')
Select @ SQL
EXEC master .. xp_mongoshell @ SQL
IF @ Error <> 0 BEGIN
Print 'An error occurred while generating the file. Please check it! '+ @ Error
END
GO