Msg 102，Level 15，State 1，Line 6'='附近的语法不正确

Question

我需要我的存储过程帮助，我不断收到此错误，但找不到真正的错误。

Msg 102，Level 15，State 1，Line 6 '='附近的语法不正确。

我的代码：

DECLARE @Batch_Num_For_Sending BIGINT; 
DECLARE @IPLocal NVARCHAR(15); 

SET @IPLocal = (
     SELECT Local_IPAddress 
     FROM [RFIDSync].[dbo].[RFID_LinkSetup] 
     WHERE Link_ID = 1 
     ); 

DECLARE @IPMain NVARCHAR(15); 

SET @IPMain = (
     SELECT Main_IPAddress 
     FROM [RFIDSync].[dbo].[RFID_LinkSetup] 
     WHERE Link_ID = 1 
     ); 

DECLARE @LocationProj NVARCHAR(50); 

SET @LocationProj = 'Test_Project'; 
SET @Batch_Num_For_Sending = (
     SELECT MIN(Batch_Id) 
     FROM [db].[dbo].[LOCALBATCH] 
     WHERE Batch_Status = 0 
     ) 

SELECT @Batch_Num_For_Sending 

EXEC (
     ' 
     SELECT Sync_Batch_Num FROM OPENQUERY([' + @IPMain + '\SQLEXPRESS], ''SELECT Sync_Batch_Num 
      FROM [db].[dbo].[SYNCBATCH] WHERE [Location] = ''''' + @LocationProj + ''''' 
      '') 
      WHERE Sync_Batch_Num = ' + @Batch_Num_For_Sending + ' 
     ' 
     ) 

Answer 1

下面的解决方案应该添加缺少的'：

DECLARE @SQL NVARCHAR(MAX) = 'SELECT Sync_Batch_Num FROM OPENQUERY([' + @IPMain + '\SQLEXPRESS], ''SELECT Sync_Batch_Num 
       FROM [db].[dbo].[SYNCBATCH] WHERE [Location] = ''''' + CONVERT(NVARCHAR(50), @LocationProj) + ''''''') 
       WHERE Sync_Batch_Num = ' + CONVERT(NVARCHAR(50), @Batch_Num_For_Sending) + ''; 

    PRINT @SQL 

    EXEC (@SQL) 

尝试下一次检测到错误打印结果的查询。

Answer 2

您的查询可能具有不同的引号比它需要被结束了。 首先将动态sql分配给变量，并在将其传递给EXEC（）语句之前查看它的输出。

尝试下面的一个

DECLARE @Batch_Num_For_Sending bigint; 
DECLARE @IPLocal nvarchar(15); 
SET @IPLocal = (SELECT Local_IPAddress FROM [RFIDSync].[dbo].[RFID_LinkSetup] WHERE Link_ID = 1); 
DECLARE @IPMain nvarchar(15); 
SET @IPMain = (SELECT Main_IPAddress FROM [RFIDSync].[dbo].[RFID_LinkSetup] WHERE Link_ID = 1); 
DECLARE @LocationProj nvarchar(50); SET @LocationProj = 'Test_Project'; 

DECLARE @SQ VARCHAR(4) = '''' 
DECLARE @SQL VARCHAR(max) = '' 

SET @Batch_Num_For_Sending = (SELECT MIN(Batch_Id) FROM [db].[dbo].[LOCALBATCH] WHERE Batch_Status = 0) 
SELECT @Batch_Num_For_Sending 

SET @SQL = 'SELECT Sync_Batch_Num FROM OPENQUERY(['[email protected]+'\SQLEXPRESS],' + @SQ + 'SELECT Sync_Batch_Num 
     FROM [db].[dbo].[SYNCBATCH] WHERE [Location] =' + @SQ + @Locatio[email protected] + ') 
     WHERE Sync_Batch_Num = '+CAST(@Batch_Num_For_Sending AS VARCHAR) 


EXEC(@SQL)