SET - EXECUTE in TSQL with passed variable

Question

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;

HABO · Accepted Answer · 2019-10-26T21:04:28.607

1

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.

edited Oct 26 '19 at 21:04

answered Oct 26 '19 at 20:12

HABO

15,314
5
39
57

Thank you! What does the N'... do? – trying2figureitout Oct 26 '19 at 20:17
One additional clarification question. How or why does the variable, @v_inputfile, work in the FROM...OPENROWSET(BULK, piece that is part of the quoted string but it won't work in the SELECT piece above just above it? I'll test using the parameters with the sp_executesql as suggested. – trying2figureitout Oct 26 '19 at 20:31
Tried the following but I'm getting an Incorrect syntax near ', ' error – trying2figureitout Oct 26 '19 at 20:44
(1) The `N` prefix indicates a [Unicode constant](https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver15#unicode-strings). (B) Print out the SQL strings for the two queries. The first contains the _value_ of `@v_inputfile` as an argument to `OPENROWSET` while the second contains a _reference_ to `@v_inputfile` as column value for `insert`. (iii) The error is a bit vague. I've made some corrections to the example code. – HABO Oct 26 '19 at 21:00
That worked! Thank you very much! So I'm curious, but why does the revised code work? A.) Execute 'sp_executesql' --> In your original version you just had the 'sp_executesql' piece which didn't work for me. B.) '@v_inputfile = @v_inputfile' --> Why do you need to do this part which seems redundant? – trying2figureitout Oct 27 '19 at 03:27
`sp_executesql` is a built-in SP. I assumed that you would `execute` it. My bad. The `@v_inputfile = @v_inputfile` is perplexing because the left-hand side is the variable within the string to be executed (and the parameter defined in the `execute` statement) and the right-hand side is the variable you declared at the beginning of your code. As a rule it makes sense to give the two different names, e.g. `'FROM OPENROWSET(BULK '''+ @p_inputfile +''', SINGLE_BLOB) AS x;'` and `@params = N'@p_inputfile varchar(50)', @p_inputfile = @v_inputfile` to distinguish the _parameter_ from the _variable_. – HABO Oct 27 '19 at 03:35
2

Aside: Accepting answers is part of the etiquette at StackOverflow. See [How Does Accepting An Answer Work?](http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work) for more information. – HABO Oct 27 '19 at 03:38

score 0 · Answer 2 · answered Oct 27 '19 at 15:02

The following works.

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;'
EXECUTE sp_executesql @v_stmt = @v_sql, 
    @params = N'@v_inputfile varchar(50)', @v_inputfile = @v_inputfile;

SET - EXECUTE in TSQL with passed variable

2 Answers2