0

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.

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 
  • 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. – Shideh 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 – Shideh 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

Not the answer you're looking for? Browse other questions tagged or ask your own question.