动态 SQL 和执行 SQL 文本 - 'Incorrect Syntax Near Column'和'Unclosed Quotation Mark After the Character S

Question

我在弄清楚哪里有语法错误和未闭合引号时遇到了问题。

当我运行此查询时：

    query.CommandText = "DECLARE @sql varchar(max);"
    query.CommandText = query.CommandText + " DECLARE @cond varchar(max);"
    query.CommandText = query.CommandText + " SET @cond = replace(replace(@search, '''', ''''''),' ','"" and ""');"
    query.CommandText = query.CommandText + " SET @sql = 'SELECT distinct datawarehouse.dbo.orderformdump.itemno, basedescription,upc,CAST((SELECT [UNITPRICE] FROM PPPLTD.dbo.[ICPRICP] WHERE [ITEMNO] = replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],''-'','''') AND [PRICELIST] = (select top 1 priclist from PPPLTD.dbo.ARCUS where IDCUST = (select top 1 CUSTID from PPPLTD.dbo.WEBLOGINACCESS where [USER] = ''" + Session("Username") + "'')) and [CURRENCY] = ''CDN'' and DPRICETYPE = 1) AS DECIMAL(18,2)),caseqty, qty AS userquantity FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,''-'','''') and orduniq not in (select orduniq from pppltd.dbo.weboeordsubmit) and WEBOEORDD.ORDUNIQ in (select orduniq from pppltd.dbo.weboeordh where [user] = ''" + Session("Username") + "'') where (allowinbc = ''Yes'' or allowinab = ''Yes'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and (contains((basedescription, category, datawarehouse.dbo.orderformdump.description, itembrand, itemgroup, itemname, datawarehouse.dbo.orderformdump.itemno, itemsubtype, itemtype, subcat, upc), ''""' + @cond + '""'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'or (select top 1 1 from PPPLTD.dbo.ICITEMO where OPTFIELD like ''UPC%'' and VALUE like ''%' + replace(@search, '''', '''''') + '%'''"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) order by DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION'"
    query.CommandText = query.CommandText + " EXECUTE (@sql)"

但是，当我添加行OR REPLACE(ITEMBRAND, '''''', '''') LIKE ''%' + @search + '%''时，查询运行良好

它给了我错误：

"UPC"附近的语法不正确。

和

字符串"和 ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno） 不为空）顺序后的未闭合引号由 DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION"排序。

这是给我带来麻烦的完整查询：

    query.CommandText = "DECLARE @sql varchar(max);"
    query.CommandText = query.CommandText + " DECLARE @cond varchar(max);"
    query.CommandText = query.CommandText + " SET @cond = replace(replace(@search, '''', ''''''),' ','"" and ""');"
    query.CommandText = query.CommandText + " SET @sql = 'SELECT distinct datawarehouse.dbo.orderformdump.itemno, basedescription,upc,CAST((SELECT [UNITPRICE] FROM PPPLTD.dbo.[ICPRICP] WHERE [ITEMNO] = replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],''-'','''') AND [PRICELIST] = (select top 1 priclist from PPPLTD.dbo.ARCUS where IDCUST = (select top 1 CUSTID from PPPLTD.dbo.WEBLOGINACCESS where [USER] = ''" + Session("Username") + "'')) and [CURRENCY] = ''CDN'' and DPRICETYPE = 1) AS DECIMAL(18,2)),caseqty, qty AS userquantity FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,''-'','''') and orduniq not in (select orduniq from pppltd.dbo.weboeordsubmit) and WEBOEORDD.ORDUNIQ in (select orduniq from pppltd.dbo.weboeordh where [user] = ''" + Session("Username") + "'') where (allowinbc = ''Yes'' or allowinab = ''Yes'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and (contains((basedescription, category, datawarehouse.dbo.orderformdump.description, itembrand, itemgroup, itemname, datawarehouse.dbo.orderformdump.itemno, itemsubtype, itemtype, subcat, upc), ''""' + @cond + '""'') OR REPLACE(ITEMBRAND, '''''', '''') LIKE ''%' + @search + '%'' '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'or (select top 1 1 from PPPLTD.dbo.ICITEMO where OPTFIELD like ''UPC%'' and VALUE like ''%' + replace(@search, '''', '''''') + '%'''"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) order by DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION'"
    query.CommandText = query.CommandText + " EXECUTE (@sql)"

我尝试打印语句，但仍然找不到错误所在。

我将在第 6 行的末尾添加新的 SQL 行。

谢谢。

Answer 1

尝试将REPLACE(ITEMBRAND, '''''', '''')更改为REPLACE(ITEMBRAND, '''''''', '''')

添加了另一个'，因此它不是返回REPLACE(ITEMBRAND ,''' ,'')，而是返回REPLACE(ITEMBRAND ,'''' ,'')