CODEWITHSUNDEEP
sqlsql-servert-sqlexecopenrowset
nikogram
nikogram

Reputation: 61

String concatenation in SQL doesn't work

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

Upvotes: 1

Views: 1083

Answers (2)

SqlZim
SqlZim

Reputation: 38073

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:

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

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.

Upvotes: 1

Related Questions