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: