This tip continues the system stored procedure series with a routine to create a comma-delimited file containing data from several columns.

A comma-delimited data file, also known as a comma-separated value (CSV) file, is typically opened with Microsoft Excel and it often contains a simple data extract.  It gets its name from the fact that a comma is placed between adjacent column values within each row.  Excel uses each comma as a signal to place the following value in the next column of the worksheet.

SQL Server provides at least four different ways to create a data file, but none of them are easily used from T-SQL code.  A DTS package could be used, but that must be developed outside of T-SQL and it involves a cumbersome execution process.  A data file can be created using a FileSystemObject through OLE automation, but that can be tricky to do properly.  The osql command prompt utility can be used, but that involves a cumbersome execution process.  The bcp (bulk copy program) command prompt utility can be used, but that involves a cumbersome execution process as well.

This stored procedure acts as a wrapper around two of the above methods and shields you from the details of implementation.  It offers a choice between the OLE automation method and the bcp method.

The OLE automation method of creating a data file is an interesting approach, but it's not necessarily recommended for this particular task.  There are serious security implications with allowing users to execute the extended stored procedures that support OLE automation.  The method is also very slow with large sets of data.  It does have the advantage of being able to precisely control what goes into the file, but in most cases such flexibility is not necessary.  The OLE automation method is included in this routine as much for the sake of example as for adding useful functionality.

The bcp method of creating a data file is strongly preferred (and it's the default method) because it generally provides much better performance than the OLE automation method.  This stored procedure uses the xp_cmdshell extended stored procedure to run the bcp utility.  There are security implications with using xp_cmdshell.  Normally, only a sysadmin can execute xp_cmdshell, but others can be granted permission.  The SQL Agent proxy account is another way to control access to the potentially dangerous tool.  See BOL for further details about xp_cmdshell.  The osql method would be very similar to the bcp method for this purpose, in both usage and effect.

The SQL code in Listing 1 creates a system stored procedure named sp_SaveDelimitedColumns.  The routine creates a file at the specified location and puts the specified set of data into the file.

The sp_SaveDelimitedColumns stored procedure accepts five parameters, but only one of them is required.

The first parameter (@PCWrite) specifies the location for the data file.  The paramter must provide a complete path, including file name, to a location where the SQL Server service account is allowed to create a file.  If no value is provided the results are returned as text.

The second parameter (@DBFetch) specifies the set of data for the file.  The parameter can be a table name (including a temporary table if using the bcp method), view name, user-defined function call, or SELECT statement.

The third parameter (@DBWhere) is optional and it specifies a WHERE clause to filter the data specified by the @DBFetch parameter.

The fourth parameter (@DBThere) is optional and it specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.

The fifth parameter (@DBUltra) is optional and it specifies which method of creating a data file to use.  A value of zero (0) uses the bcp method.  A value of one (1) uses the OLE automation method.

It's awkward to embed single quotes in a string literal with T-SQL.  For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch, @DBWhere, and @DBThere.  This feature is demonstrated in an example below.

The sp_SaveDelimitedColumns stored procedure creates a file at the @PCWrite location (using the @DBUltra method) and places in it the columns from the @DBFetch data set (optionally filtered/sorted by @DBWhere/@DBThere).  The values for any column that is not numeric are enclosed in quotation marks (such as "String") in case the values contain the column delimiter character (a comma).  The values for each column are delimited, or separated, with commas (such as "String1",0,"String2").

The T-SQL code for this stored procedure is structured very similarly to that of the previous tip, but it's much more involved in the middle because it must convert values of various data types into values of a character data type before putting them into a text file.

The examples below use a NULL value for the destination file parameter.  Please substitute a complete path, including file name, that's appropriate for your environment.

This example exports selected customers from the Northwind database.

USE Northwind EXECUTE sp_SaveDelimitedColumns
NULL,
'Customers',
'Country = ^USA^'

This example exports a product list sorted by ProductID from the Northwind database.

USE Northwind EXECUTE sp_SaveDelimitedColumns
NULL,
'Products',
NULL,
'ProductID'

I hope you find this system stored procedure to be useful.