SQL 儲存過程，具有一個參數和多個隨時間變化的變數

我正在嘗試建構一個帶有隨時間變化的變數的儲存過程。關於SP執行（在數據庫下的可程式性中右鍵點擊SP並執行）導致

錯誤輸出：

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'file20160303FROMx:'.

(1 row(s) affected)

我真的很願意接受任何回饋，如果需要的話，可以進行完整的重新設計。還是這個領域的小學生。

腳本：

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[procname]    Script Date: 2016-03-03 09:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--=============================================
--Author:      <Author,,Name>
--Create date: <Create Date,,>
--Description: <Description,,>
--=============================================
ALTER PROC [dbo].[procname]
@p1 nvarchar(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @insertvariable NVARCHAR(MAX)
Declare @var1 NVARCHAR (32) = 'x:\xxxx\xxxx\file\err';
Declare @var2 NVARCHAR (15) = convert(varchar, getdate(),112) + '.' + replace(convert(varchar, getdate(),108),':','')
Declare @var3 NVARCHAR (MAX) = @var1 + @var2 + '.log';
Declare @var4  NVARCHAR(MAX)= 'x:\xxxx\xxxx\file';
Declare @var5 NVARCHAR(MAX) = @var2 + @p1 + '.tab.txt';
Declare @formatFile NVARCHAR (MAX) = 'x:\xxxx\xxxx\formatFile';

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
   (
  FIRSTROW = 1,
  ORDER (Id),
  CODEPAGE = Finnish_Swedish_CI_AS,
  FIELDTERMINATOR = \t,
  ROWTERMINATOR = 0x0a,
  TABLOCK,
  ERRORFILE = @var3,
  FORMATFILE = @formatFile, 
  KEEPNULLS
  );'
  exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
  end

您的連接字元串中缺少空格，您還需要引用您的文件名。我也看不到混合連接和使用參數呼叫 sp_executesql 的意義。

嘗試更改此部分：

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
   (
  FIRSTROW = 1,
  ORDER (Id),
  CODEPAGE = Finnish_Swedish_CI_AS,
  FIELDTERMINATOR = \t,
  ROWTERMINATOR = 0x0a,
  TABLOCK,
  ERRORFILE = @var3,
  FORMATFILE = @formatFile, 
  KEEPNULLS
  );'
  exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
  end

為此，請注意連接 @p1 和 @var5 以及單引號的空格：

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename ' + @p1 + ' FROM ''' + @var5 + ''' WITH 
   (
  FIRSTROW = 1,
  ORDER (Id),
  CODEPAGE = ''Finnish_Swedish_CI_AS'',
  FIELDTERMINATOR = \t,
  ROWTERMINATOR = 0x0a,
  TABLOCK,
  ERRORFILE = ''' + @var3 + ''',
  FORMATFILE = ''' + @formatFile + ''', 
  KEEPNULLS
  );'
  exec sp_executesql @insertvariable
  end

引用自：https://dba.stackexchange.com/questions/131156