Sql-server – Receiving error while creating stored procedure to insert Excel files into a table

querysql serversyntaxt-sql

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.

Best Answer

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
Related Question