1

I need to have a where clause in OPENROWSET but it is not always fix. It might be null sometimes. I have a procedure as you can see here :

ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000)
    ,@FileName VARCHAR(2000)
    ,@ExcelName VARCHAR(2000)
    ,@SheetName VARCHAR(100)
    ,@Header VARCHAR(50)
    ,@WhereClause VARCHAR(1000)
    ,@ResultOpenExcel INT OUTPUT
AS
BEGIN
    DECLARE @Detail NVARCHAR(max)
        ,@RowFlag BIT = 0

    SET @ResultOpenExcel = 0

    BEGIN TRY
        BEGIN TRANSACTION OpenExcel

        DECLARE @Provider NVARCHAR(2000);

        SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

        DECLARE @date DATETIME

        SET @date = getdate()

        DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp 
   INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]' + @WhereClause + ' );

     insert into ' + @TableName + '
     select * 
     from #TTT
     drop Table #TTT'

        PRINT @SQL

        EXEC (@SQL)

        --------------------------------
        DECLARE @RowCount INT = @@ROWCOUNT;

        IF (@RowCount > 1)
            SET @RowFlag = 1
        SET @ResultOpenExcel = 1

        COMMIT TRANSACTION OpenExcel
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION OpenExcel

        SET @ResultOpenExcel = 0

        SELECT @Detail = ErrorMessage
        FROM dbo.GetErrorInfo()

        EXECUTE [TestTest].[dbo].[Sp_Log] @TableName
            ,'[dbo].[OpenExcel]'
            ,@Detail
    END CATCH

    RETURN @ResultOpenExcel
END

I need to pass the "Where Clause" it might sometimes have a content and sometimes not. For instance it might be like this : Where age >'23'. I'm testing my procedure but it gives me this error :

Incorrect syntax near 23

What is wrong with the openrowset part of my query?

the @Print part show me this in my example:

"SELECT * ,'SMR1' AS ExcelName ,'Oct  6 2018  4:07PM' AS TimeStamp 
   INTO #TTT
     from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=C:\NewExcelFile\SMR1.xlsx;hdr=yes',
                     'select * from [Sheet1$]where Age >'23' );"

It seems that my query is correct so what is wrong here?

1

I've solved my own problem by rewriting my code like this:

DECLARE @date DATETIME

SET @date = getdate()

DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp 
    INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]'')' + @WhereClause + ';
     insert into ' + @TableName + '
     select * 
     from #TTT
     drop Table #TTT'

PRINT @SQL

EXEC (@SQL)

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.