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

Solution

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: