Variable in Openrowset

[andyc209]

i am trying to pass a variable into an Openrowset - i have seen its not possible this way but not sure what the solution will be.

I need to do it as i have multiple files in need to pull data in from, my incorrect code is below:

DECLARE @XFILENAME AS VARCHAR(50)
DECLARE CUR_UPDATE SCROLL CURSOR FOR
SELECT PATH FROM TBL_FILENAME
OPEN CUR_UPDATE

--LOOP THROUGH RETRIEVED FILES --
FETCH NEXT FROM CUR_UPDATE
INTO
@XFILENAME

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT * INTO DBO.TEMP_NEWLEADIMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database='+ @XFILENAME +'',
'SELECT * FROM [NewLeads$]')

FETCH NEXT FROM CUR_UPDATE
INTO
@XFILENAME
END

CLOSE CUR_UPDATE
DEALLOCATE CUR_UPDATE

I get the error 'Incorrect Syntax near +' as i assume the OPENROWSET is not expecting the variable.

 

[djj55]

You need to use dynamic SQL. Look at sp_executesql

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[andyc209]

i have seen this done but cannot see how to fit it into my code - most of the exapmles i have found on google are delete etc rather than the insert into i have

 

[djj55]

DECLARE @XFILENAME AS VARCHAR(50), @strSQL NVARCHAR(2000);
DECLARE CUR_UPDATE SCROLL CURSOR FOR
    SELECT PATH FROM TBL_FILENAME
OPEN CUR_UPDATE

--LOOP THROUGH RETRIEVED FILES --
FETCH NEXT FROM CUR_UPDATE INTO @XFILENAME

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'SELECT * INTO DBO.TEMP_NEWLEADIMPORT FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
    ''Excel 12.0 Xml;HDR=YES;Database=''' + @XFILENAME + ''',''SELECT * FROM [NewLeads$]'')'

    EXECUTE sp_executesql @strSQL

    FETCH NEXT FROM CUR_UPDATE INTO @XFILENAME 
END

CLOSE CUR_UPDATE
DEALLOCATE CUR_UPDATE

Please notice the NVARCHAR as it needs to be Unicode.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[andyc209]

excellent thankyou so much - seems to be fine