I want pass dynamic URL of excel to "OPENROWSET".
NOTE - I am passing returned result of excel file to cursor. I want to pass file path to "@excelpath", I have tried many ways but its giving syntax error.
ALTER procedure [dbo].[import_excel]
(
@excelpath as nvarchar(max)
)
as
begin
set nocount on
DECLARE insert_cursor CURSOR FOR
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\memberdata.xlsx', [Sheet1$])
OPEN insert_cursor;
FETCH NEXT FROM insert_cursor
INTO @id_number, @memberName
WHILE @@FETCH_STATUS = 0
BEGIN
-- body of cursor
FETCH NEXT FROM insert_cursor
INTO @id_number, @memberName
END
CLOSE insert_cursor;
DEALLOCATE insert_cursor;
END
You have to build your query using dynamic SQL, as shown in this question. It would probably be simplest for you to insert the data from your query into a permanent table, then run the cursor over the permanent table. In that way you minimize the amount of SQL you need to work with dynamically.