Output table from database or SP directly into excel file using SQL.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [SP_EXCEL_EXPORT_FORMAT]
    (
      @dbName VARCHAR(100) = '[1308]--DataBaseName' ,
      @sql VARCHAR(8000) = 'SELECT Employee_ID,Employee_Name,Employee_Address,Employee_City FROM dbo.Employee_Master' ,
      @fullFileName VARCHAR(100) = 'D:\BhavdipTala.xls'
    )
AS
    BEGIN
        IF @sql = ''
            OR @fullFileName = ''
            BEGIN
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
-- if DB isn't passed in set it to master
        SELECT  @dbName = 'use ' + @dbName + ';'
--print 'use'
        IF OBJECT_ID('##TempExportData') IS NOT NULL
            DROP TABLE ##TempExportData
        IF OBJECT_ID('##TempExportData2') IS NOT NULL
            DROP TABLE ##TempExportData2
-- 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))
--print '##TempExportData'
        EXEC(@dbName + @tempSQL)
        IF @@error > 0
            BEGIN
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
-- 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'

--print 'columnNames: ' + @columnNames
--print 'columnConvert: ' + @columnConvert

-- 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(REPLACE(REPLACE(@columnNames, ',', ''', '''), '[',
                                  ''), ']', '')
                + ''', ''1'') t order by [temp##SortID]'

--print '##TempExportData2'

        EXEC (@sql)
        PRINT @sql
-- build full BCP query
        DECLARE @bcpCommand VARCHAR(8000)
        SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
        SET @bcpCommand = @bcpCommand + ' ' + @fullFileName
            + ' -w -T -U sa -P sa","-CRAW'
        EXEC master..xp_cmdshell @bcpCommand
--print 'bcpCommand'
        IF @@error > 0
            BEGIN
--print 'error>0'
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
        DROP TABLE ##TempExportData
        DROP TABLE ##TempExportData2
        SET @columnNames = ' '
        SET @columnConvert = ' '
        SET @tempSQL = ' '
        SELECT  1 AS ReturnValue

    END