Export Query Results to Excel

-- Export Query results to Excel with Column names in T-SQL
--
-- http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
--
-- NOTE: xp_cmdshell must be enabled for stored proc to work. To enable
-- xp_cmdshell, run run the following statments:
--
--     use Master;
--     go
--     exec master.dbo.sp_configure 'show advanced options', 1;
--     reconfigure with override;
--     go
--     exec master.dbo.sp_configure 'xp_cmdshell', 1;
--     reconfigure with override;
--     go
--

use master;
go

if object_id('usp_ExportData') is not null
    begin
        drop proc usp_ExportData
    end;
go

create proc usp_ExportData
(
    @dbName varchar(100) = 'master',
    @sql varchar(5000) = null,
    @fullFileName varchar(100) = null
)
as
    begin
        if @sql is null or @fullFileName is null
            begin
                select 1 as ReturnValue; -- failure
                return;
            end;

        -- if DB isn't passed in set it to master
        select @dbName = 'use ' + @dbName + ';';

        if exists (select * from tempdb..sysobjects where id = object_id('tempdb..##TempExportData'))
            begin
                DROP TABLE ##TempExportData
            end;
        if exists (select * from tempdb..sysobjects where id = object_id('tempdb..##TempExportData2'))
            begin
                DROP TABLE ##TempExportData2
            end;

        -- insert data into a global temp table
        declare @columnNames varchar(8000),
            @columnConvert varchar(8000),
            @tempSQL varchar(8000);

        select @tempSQL = left(@sql, charindex('from', @sql) - 1) + ' into ##TempExportData ' + substring(@sql, charindex('from', @sql) - 1, len(@sql));
        exec (@dbName + @tempSQL);

        -- build 2 lists
        -- 1. column names
        -- 2. columns converted to nvarchar
        select @columnNames = coalesce(@columnNames + ',', '') + column_name,
            @columnConvert = coalesce(
                @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name +
                    case
                        when data_type in ('datetime', 'smalldatetime')
                            then ',121'
                        when data_type in ('numeric', 'decimal')
                            then ',128'
                        when data_type in ('float', 'real', 'money', 'smallmoney')
                            then ',2'
                        when data_type in ('datetime', 'smalldatetime')
                            then ',120'
                        else ''
                    end + ') as ' + column_name
        from tempdb.INFORMATION_SCHEMA.Columns
        where table_name = '##TempExportData';

        -- execute select query to insert data and column names into new temp table
        select @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]';
        exec (@sql);

        -- build full BCP query
        select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW -T';

        -- execute BCP
        exec master..xp_cmdshell @sql;

        if exists (select * from tempdb..sysobjects where id = object_id('tempdb..##TempExportData'))
            begin
                DROP TABLE ##TempExportData
            end;
        if exists (select * from tempdb..sysobjects where id = object_id('tempdb..##TempExportData2'))
            begin
                DROP TABLE ##TempExportData2
            end;
    end;
go

--
-- Example Usage
--

declare
    @sql varchar(6800),
    @dbName varchar(100),
    @fullFileName varchar(100);

select
    @dbName = 'northwind',
    @sql = 'select * from orders',
    @fullFileName = 'e:\sql.xls';

exec master..usp_ExportData @dbName, @sql, @fullFileName;