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)

        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



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.


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) 

  • 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
    Sep 29 '18 at 9:45
  • 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
    – user157749
    Sep 29 '18 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy