Query To CSV

This stored procedure takes any single-column SELECT statement as a string and returns its results as one delimited list - effectively CSV in a cell. Optional front and back padding lets you wrap each value (for example in quotes) without touching the source query.

Example: EXEC QueryToCSV @SQLStatement = 'SELECT name FROM sys.databases', @Delimiter = ',', @FrontPadding = '"', @BackPadding = '"'

CREATE PROCEDURE QueryToCSV
(
	@SQLStatement VarChar(MAX),
	@Delimiter VarChar(10) = ',',
	@FrontPadding VarChar(128) = '',
	@BackPadding VarChar(128) = ''
)
AS
BEGIN
	DECLARE @SQL VarChar(MAX)

	SET @SQL =
		'DECLARE @List	VarChar(MAX) ' +
		'DECLARE @Value	VarChar(MAX) ' +

		'SET @List = '''' ' +

		'DECLARE Items CURSOR FAST_FORWARD FOR ' +
			@SQLStatement + ' ' +
		'OPEN Items ' +

		'FETCH FROM Items INTO @Value ' +

		'WHILE(@@FETCH_STATUS = 0) ' +
		'BEGIN ' +
			'IF @Value IS NOT NULL ' +
			'BEGIN ' +
				'SET @List = @List + ''' + @FrontPadding + ''' + @Value + ''' + @BackPadding + ''' ' +
			'END ' +
			
			'FETCH FROM Items INTO @Value ' +

			'IF(@@FETCH_STATUS = 0 AND @Value IS NOT NULL) ' +
			'BEGIN ' +
				'SET @List = @List + ''' + @Delimiter + ''' ' +
			'END ' +
		'END ' +

		'CLOSE Items DEALLOCATE Items ' +

		'SELECT @List as List '
	EXEC(@SQL)
END
GO