SQL Server – Using ‘Where Clause’ in OPENROWSET as Variable

openrowsetsql servert-sql

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?

Best Answer

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)