I was wondering if you could tell me what is wrong with my query?
@TableName is an input parameter.
Why do I have to declare it again?
CREATE PROCEDURE [dbo].[OpenExcel] @FileName nvarchar(2000), @TableName varchar(2000) AS BEGIN DECLARE @Provider nvarchar(2000) SET @Provider = 'Microsoft.ACE.OLEDB.12.0' DECLARE @etValue nvarchar(4000) SET @RetValue = 'select * from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=no' + ''', ''select " from [Sheet1$]'' )' DELETE FROM @TableName INSERT INTO @TableName EXEC(@RetValue) END
The error message I receive is:
Msg 1087, Level 16, State 1, Procedure OpenExcel, Line 26
Must declare the table variable "@TableName".
Well I'm trying to pass the Excel path (
@FileName) and table name (
@TableName; in which the data should be inserted) to the stored procedure.
How can I achieve that?
I have different tables with different structures. Please help me with this issue.