Getting error while export data from excel

I am getting following error

ERROR:- Incorrect syntax near '+'.

while executing following T-Sql

DECLARE @DatabasePath VARCHAR(MAX)
SET @DatabasePath = 'E:\ABC.xls'
开发者_高级运维
INSERT INTO [dbo].[Table_1]
SELECT  *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
        'Excel 8.0;Database='+@DatabasePath+'',
        'SELECT * FROM [Sheet1$]') AS xlsTable 

Is there something wrong in T-Sql. Actually i want to create a stored procedure where i will pass excel sheet path in @DatabasePath input parameter.

---

I dont thing this will work for you.

From OPENROWSET (Transact-SQL)

'datasource'

Is a string constant that corresponds to a particular OLE DB data source.

Have you tried something like

DECLARE @DatabasePath VARCHAR(MAX) 
SET @DatabasePath = 'C:\tada.xlsx'

DECLARE @RowSetString VARCHAR(MAX)
SELECT @RowSetString = 'SELECT  * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + @DatabasePath + ''',''SELECT * FROM [Sheet1$]'')'

SELECT @RowSetString

EXEC(@RowSetString)

---

You will need to use dynamic SQL:

DECLARE @DatabasePath VARCHAR(MAX)
SET @DatabasePath = 'E:\ABC.xls'

DECLARE @sql      nvarchar(MAX)
SET @sql = '
INSERT INTO [dbo].[Table_1]
SELECT  *
FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
        ''Excel 8.0;Database=' + @DatabasePath + ',
        ''SELECT * FROM [Sheet1$]'') AS xlsTable'

EXEC sp_executesql @sql