String concatenation in SQL doesn't work

Question

I am wondering why the following doesn't work:

INSERT INTO @Data2 (FileName,Field) 
SELECT @FileName as FileName, * FROM OPENROWSET(BULK  '\\serverpath\' + @FileName  , SINGLE_CLOB) AS Contents

I tried also the second approach but with this one I get the error message that the variable isn't declared:

declare @path nvarchar(255) = 'SELECT @FileName as FileName, * FROM OPENROWSET(BULK ''\\serverpath\' + @FileName + ''', SINGLE_CLOB) AS Contents'

INSERT INTO @Data2 (FileName,Field)
EXEC(@path)

Can somebody help? Thanks

Explain what "doesn't work" means.

Gordon Linoff
– Gordon Linoff

2017-06-09 12:10:47 +00:00
Commented Jun 9, 2017 at 12:10 — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2017 at 12:10

SqlZim · Accepted Answer · 2017-06-09 12:32:18Z

2

You can not pass @FileName as FileName using exec, but you can using sp_executesql -- but you still can not pass @FileName as part of openrowset():

declare @path nvarchar(4000) = N'
  select @FileName as FileName, * 
  from openrowset(bulk ''\\serverpath\' + @FileName + '''
    , single_clob) as Contents'

insert into @Data2 (FileName,Field)
exec sp_executesql, N'@FileName nvarchar(1024))',@FileName

Or directly concatenating it like you are for the bulk source:

declare @path nvarchar(4000) = N'
  select '+quotename(@FileName,'''')+' as FileName, * 
  from openrowset(bulk '+quotename('\\serverpath\'+@FileName,'''')+'
    , single_clob) as Contents'

insert into @Data2 (FileName,Field)
exec sp_executesql @path

reference:

edited Jun 9, 2017 at 12:32

answered Jun 9, 2017 at 12:23

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gottfried Lesigang · Accepted Answer · 2017-06-09 12:30:30Z

You cannot use OPENROWSET with a variable path. Check this:

--This works

DECLARE @Data2 TABLE(Field VARBINARY(MAX));
INSERT INTO @Data2 (Field) 
SELECT * FROM OPENROWSET(BULK  'F:\Privat\StackOverflow\test.xml'  , SINGLE_BLOB) AS Contents;
SELECT CAST(Field AS NVARCHAR(MAX)) FROM @Data2;

--This breaks at syntax check

DECLARE @FileName AS VARCHAR(1000)='F:\Privat\StackOverflow\test.xml';
INSERT INTO @Data2 (Field) 
SELECT * FROM OPENROWSET(BULK  @FileName  , SINGLE_BLOB) AS Contents;
SELECT CAST(Field AS NVARCHAR(MAX)) FROM @Data2;

--This works, but needs a physically created table. Your table variable is out of scope within the EXEC-execution

CREATE TABLE Data2(Field VARBINARY(MAX));
DECLARE @FileName AS VARCHAR(1000)='F:\Privat\StackOverflow\test.xml';
DECLARE @cmd VARCHAR(MAX)='INSERT INTO Data2(Field) SELECT * FROM OPENROWSET(BULK ''' +  @FileName + ''', SINGLE_BLOB) AS Contents;'
EXEC(@cmd);
SELECT * FROM Data2;
GO
DROP TABLE Data2;

Instead of the physically created table you might use an output parameter.

Collectives™ on Stack Overflow

String concatenation in SQL doesn't work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related