Export Data to Excel Using Template

Create PROCEDURE SP_ExportDataUsingTemplate @File_Name as varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)

-- New File Name to be created
IF @File_Name = ''
Select @fn = 'D:\Test.xls'
ELSE
Select @fn = 'D:\' + @File_Name + '.xls'

-- FileCopy command string formation
SELECT @Cmd = 'Copy D:\Template.xls ' + @fn

-- FielCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT

-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0; Database=' + @fn

-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'')
select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')

exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'')
select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')

SET NOCOUNT OFF
END

/*------------sample statements-------------

use pubs
exec SP_ExportDataUsingTemplate 'AuthorsAndOrders'
select * from sales (NOLOCK)
select * from authors

*/-------------------------------------------