SET - EXECUTE in TSQL with passed variable

I'm working with an OPENROWSET (BULK...) insert where I'm inserting a small XML file into a table before ultimately shredding and transforming it. The following works just fine (many thanks to other posts).

DECLARE @v_inputfile AS varchar(50)
DECLARE @v_xmlfile AS xml
DECLARE @v_sql AS nvarchar(1000)
DECLARE @v_loadeddatetime AS DATETIME

SET @v_inputfile='E:\inputfile.xml'
SET @v_sql= 'INSERT INTO'+
    ' Tbl1_XMLStaging1 (XMLData, LoadedDateTime)' +
    ' SELECT CONVERT(XML, BulkColumn), ' +
    ' GETDATE() ' +
    'FROM OPENROWSET(BULK '''+ @v_inputfile +''', SINGLE_BLOB) AS x;'
EXEC(@v_sql);

However, my goal is to also INSERT the InputFileName into Tbl1_XMLStaging1, but I'm having issues with @v_inputfile in the Select statement being passed as the variable into the query.

I tried the following:

DECLARE @v_inputfile AS varchar(50)
DECLARE @v_xmlfile AS xml
DECLARE @v_sql AS nvarchar(1000)
DECLARE @v_loadeddatetime AS DATETIME

SET @v_inputfile='E:\inputfile.xml'
SET @v_sql= 'INSERT INTO'+
    ' Tbl1_XMLStaging1 (XMLData, LoadedDateTime, XMLFileName)' +
    ' SELECT CONVERT(XML, BulkColumn), ' +
    ' GETDATE(), ' +
    '@v_inputfile ' +
    'FROM OPENROWSET(BULK '''+ @v_inputfile +''', SINGLE_BLOB) AS x;'
EXEC(@v_sql);

I get various error messages:

Must declare the scalar variable "@v_inputfile"

If I remove the single quotes around the variable then I get an error

Incorrect syntax near 'E:'

I've combed over several previous posts and can't seem to get past this without doing in whole other operation.

After response. I'm trying the following and getting an error

Incorrect syntax near ',

Code:

DECLARE @v_inputfile AS varchar(50)
DECLARE @v_xmlfile AS xml
DECLARE @v_sql AS nvarchar(1000)
DECLARE @v_stmt AS nvarchar(1000)
DECLARE @params AS nvarchar(100)
DECLARE @v_loadeddatetime AS DATETIME

SET @v_inputfile='E:\inputfile.xml'
SET @v_sql= 'INSERT INTO'+
    ' Tbl1_XMLStaging1 (XMLData, LoadedDateTime, XMLFileName)' +
    ' SELECT CONVERT(XML, BulkColumn), ' +
    ' GETDATE(),' +
    ' @v_inputfile ' +
    'FROM OPENROWSET(BULK '''+ @v_inputfile +''', SINGLE_BLOB) AS x;'
EXEC @v_stmt = @v_sql, @params = N'@v_inputfile varchar(50)', @v_inputfile;

Solution

The problem is that within the context of execute there is no access to variables declared in another scope. There are two ways to address this issue.

You can change the line '@v_inputfile ' + to '''' + @v_inputfile + '''' + to supply the value of the variable as a quoted string. This is somewhat fragile and shouldn't be used with input that might be untrustworthy, e.g. something entered by a user, lest you meet Bobby Tables.

Alternatively, use parameters with sp_executesql:

execute sp_executesql @stmt = @v_sql,
  @params = N'@v_inputfile varchar(50)', @v_inputfile = @v_inputfile

This is a much more robust solution and can preclude SQL injection attacks.