Can OPENROWSET parameters be built dynamically?

Question

This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-named files, such as: [Bus] + ‘-‘ + [School] + ‘-‘ + [Time (AM/MD/PM)] + ‘.xml’ ?

So for...

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

UPDATE SeatingChart
FROM OPENROWSET (...’[Bus] + ‘-‘ [School] + ‘-‘ + [Route] + ‘.xml’
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus] + ‘-‘ + [School] + ‘-‘ + [Time] + ‘.xml’

The file being imported from has columns for both the AM & PM seating charts in the same spreadsheet, so I’m not sure know how to about writing the generalized statement that will input into the

Answer 1

You could in theory do this, but it would require building the update statement with dynamic SQL. Basically, you declare a string variable, build it with metadata from the database, and then execute that string. Erland Sommarskog has an epic post on this: The Curse and Blessings of Dynamic SQL.

However, that's gonna require a lot of work and a lot of debugging.

Plus, for performance reasons, you don't really wanna fire a new OPENROWSET for every row of a table you're updating.

Instead, I'd suggest doing a single OPENROWSET call to pull all of the file contents into a temp table, and then doing your update statement from the temp table.

Answer 2

To specifically address your question you can dynamically update OPENROWSET. Below I use T-SQL's REPLACE and EXECUTE (using your UPDATE pseudo code). Should you? That's probably another discussion alltogether:

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

declare @cmdstring varchar(255)
set @cmdstring =
'UPDATE SeatingChart
FROM OPENROWSET (...[Bus]-[School]-[Route].xml
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus]-[School]-[Time].xml'

select @cmdstring = replace(@cmdstring,'[Bus]','1106')
select @cmdstring = replace(@cmdstring,'[School]','LHS')
select @cmdstring = replace(@cmdstring,'[Time]','PM')

EXECUTE(@cmdstring)