Gerrit Mantel
Programs SQL_STORED_PROCEDURE - prc_table2csv

Main program

Nameprc_table2csv
Type_descSQL_STORED_PROCEDURE
CommentSQLServer Stored Procedure

PURPOSE
Generate CSV file for a given table or view.

This procedure uses BCP to generate the CSV output file with tab separated fields.
Table- and fieldname parameter values may optionally be enclosed by [].
Adding a headerrow to the CSV output file is optional.

BCP outputs NULL values as empty fields and '' values as 0x0 character:
So don't use ISNULL(field,'') in views for NULL fields to prevent 0x0 as output characters

PARAMETERS
1 @table_name NVARCHAR(255) Table- or viewname. Temp tables are not supported.
2 @dump_path VARCHAR(255) Path to dump csv file to.
3 @filename VARCHAR(255) Optional Filename to dump csv file to. When omitted filename is derived from @table_name. Specify filename without extension.
4 @whereclause NVARCHAR(255) Optional WHERE clause to filter rows. Must begin with 'WHERE '.
5 @orderfields NVARCHAR(255) Optional (comma separated) list of fieldnames to order rows (ascending). For descending sortorder add ' DESC' to fieldnames.
6 @skipfields NVARCHAR(255) Optional (comma separated) list of fieldnames to skip.
7 @headerrow INT 1/0 Add headerrow (default) / Add no headerrow.

EXAMPLES
EXEC [dbo].[prc_table2csv] @table_name = 'Ref_setup', @orderfields = 'Parameter', @dump_path = 'C:\Data\Temp';
EXEC [dbo].[prc_table2csv] @table_name = 'Ref_setup', @orderfields = 'Parameter', @headerrow = 0, @dump_path = 'C:\Data\Temp';
EXEC [dbo].[prc_table2csv] @table_name = 'Ref_setup', @orderfields = 'Parameter', @skipfields = 'Description', @dump_path = 'C:\Data\Temp';
EXEC [dbo].[prc_table2csv] @table_name = 'CSV_Official_Hits', @orderfields = 'Chart_date,Pos', @dump_path = 'C:\Data\Temp';

USED OBJECTS
- dbo.fun_format_table_name (UDF in local database)
- dbo.fun_FolderExist (UDF in local database)
- dbo.fun_getfield (UDF in local database)
- dbo.fun_nsplit (UDF in local database) (STRING_SPLIT is avoided because the lack of ordering and trimming)
- dbo.fun_text2html (UDF in local database)
- dbo.fun_DeleteFile (UDF in local database)
- dbo.fun_text2dos (UDF in local database)
- dbo.fun_trim (UDF in local database)

HISTORY
2026-01-18 - Created procedure
2026-01-23 - Removed superfluous declaration of variable @table
2026-01-28 - Fixed error: STRING_AGG aggregation result exceeded the limit of 8000 bytes

TAGS
<program>
<description>Generate CSV file for a given table or view</description>
<minversion>14</minversion>
<generic>1</generic>
<author>Gerrit Mantel</author>
<created>2026-01-18</created>
<lastmodified>2026-01-28</lastmodified>
</program>
DescriptionGenerate CSV file for a given table or view
Minversion14.0
Generic1
AuthorGerrit Mantel
Created2026-01-18 00:00:00
Lastmodified2026-01-28 00:00:00
Xp_cmdshell1
Ole_automation0
Subprogram_cnt8
Sourcecode

Sub programs

NameType descDescriptionMinversionGenericAuthorCreatedLastmodifiedXp cmdshellOle automation
fun_DeleteFileSQL_SCALAR_FUNCTIONDelete given file(s)1Gerrit Mantel2017-10-10 00:00:002026-01-23 00:00:0001
fun_FolderExistSQL_SCALAR_FUNCTIONCheck if folder exists1Gerrit Mantel2015-11-24 00:00:002026-01-23 00:00:0001
fun_format_tablenameSQL_SCALAR_FUNCTIONFormat tablename1Gerrit Mantel2025-12-04 00:00:002025-12-04 00:00:0000
fun_getfieldSQL_SCALAR_FUNCTIONReturn the n-th field from a given string, between a given separator pattern1Gerrit Mantel2000-01-01 00:00:002024-02-09 00:00:0000
fun_nsplitSQL_TABLE_VALUED_FUNCTIONSplit delimited n-string into record values1Gerrit Mantel2005-03-21 00:00:002024-04-09 00:00:0000
fun_text2dosSQL_SCALAR_FUNCTIONReplace illegal DOS filename characters to '#'1Gerrit Mantel2000-01-01 00:00:002025-04-27 00:00:0000
fun_trimSQL_SCALAR_FUNCTIONTrim given text left and right with given character, linefeed, carriagereturn or space1Gerrit Mantel2012-04-19 00:00:002024-02-09 00:00:0000
prc_save_text_to_fileSQL_STORED_PROCEDUREWrite text variable to ansi file1Gerrit Mantel2017-01-25 00:00:002025-06-06 00:00:0001