Receiving error while creating stored procedure to insert Excel files into a table

Question

I was wondering if you could tell me what is wrong with my query?

The parameter @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

_{An image of the stored procedure}

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.

Answer 1

Here is your solution:

Create Procedure [dbo].[OpenExcel]
 @FileName nvarchar(2000),
 @TableName varchar(2000) 
AS Begin 
 declare @Provider nvarchar(2000);
 set @Provider = 'Microsoft.ACE.OLEDB.12.0';
 declare @RetValue nvarchar(4000) ;
 set @RetValue='select * from openrowset("' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=no'+''', ''select * from [Sheet1$]" )' 
 declare @Bufr nvarchar(4000) 
 set @Bufr='delete from '+@TableName+';'
 set @Bufr=@Bufr+'insert into '+@TableName exec(@RetValue) 
End 

• user157749: thank you very much . the problem is that I can see the results but not in the destination table. would you mind if you test it yourself ? I can see the result of the procedure but the data won't be inserted into the destination table.
• user157749: DECLARE @Bufr nvarchar(4000) --SET @Bufr='delete from '+@TableName+';' SET @Bufr='insert into '+@TableName + 'EXEC(@RetValue) ' EXEC (@Bufr) we have to change it this way . but this one won't work either !!! you know.. the insert part won't be executed we have to solve this issue