[SOLVED] Append Quotes for all VARCHAR Columns when exporting to csv file

Append Quotes for all VARCHAR Columns when exporting to csv file

I have a stored procedure which when run gives a table output. I want to export this procedure to a csv file but want to append double/single quotes for all the columns with a datatype CHAR/VARCHAR.

For Example:

Stored Proc O/P:

ID    Name    Address                  SSN
1      abd    9301,LeeHwy, 22031       64279100

Output in CSV File:

 1,"abd","9301,LeeHwy, 22031",64279100

Can anyone also help me on how I can use a BAT file to execute the procedure and generate this csv file.

Solution

I have found a solution for my problem. Credits also go to @Rogala (The developer who gave initial answer to the question) for triggering the idea of using system tables.

The code is as below:

DECLARE @tableName VARCHAR(Max) = '[Put your table name here]';
DECLARE @currColumns VARCHAR(Max) = NULL;
Declare @Delim CHAR(5)='''"''+'

SELECT @currColumns = COALESCE(@currColumns + ','+ CASE WHEN DATA_TYPE= 'varchar' THEN '''"'' + ' ELSE '' END + '[', '[') + COLUMN_NAME + ']'
                    + CASE WHEN DATA_TYPE = 'varchar' THEN '+ ''"''' ELSE '' END + ' as [' + COLUMN_NAME + ']' 
FROM INFORMATION_SCHEMA.Columns
WHERE  table_name = @tableName 

Set @currColumns= @Delim+@currColumns

EXEC('SELECT ' + @currColumns + ' FROM ' + @tableName);