sql - 为变量赋值的 SELECT 语句不能与数据检索操作结合使用；我不认为它是

Question

我收到错误 为变量赋值的 SELECT 语句不能与数据检索操作结合使用。我已经搜索了这个错误，但我不明白，我在哪里分配（从光标，对吗？）以及我在哪里检索这个值？

如果我在动态代码之前创建一个#表，重写动态代码以插入#表并将更新移动到动态代码之后，它就可以工作。问题是，然后我需要创建 10 # 个表......

DECLARE @SQL NVARCHAR(MAX);
DECLARE @CurrentYear NVARCHAR(MAX), @ProductId NVARCHAR(MAX);

DECLARE CurrentYearCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT ProductId, CurrentYear
FROM #SubcategoryProducts;

OPEN CurrentYearCursor;

FETCH NEXT FROM CurrentYearCursor
INTO @ProductId, @CurrentYear;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL =
    N'
WITH KeyCategories AS
(   SELECT DISTINCT MS.CompanyId
  , SP.IndustryName
  , SP.ProductName
  , MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] / 100 AS Size
  , ROW_NUMBER() OVER (PARTITION BY MS.CompanyId, SP.IndustryName
                       ORDER BY MS.[' + @CurrentYear + N'] * MS2.[' +     @CurrentYear + N'] / 100 DESC) AS Rn
FROM Sports.dbo.GlobalBrandOwnerContext AS GBOC
INNER JOIN dbo.MarketShare AS MS
    ON GBOC.CompanyId = MS.CompanyId
INNER JOIN #SubcategoryProducts AS SP
    ON SP.ProductId = MS.ProductId
    AND SP.DataTypeId = MS.DataTypeId
INNER JOIN dbo.MarketSize AS MS2
    ON MS2.ProjectCode = MS.ProjectCode
    AND MS2.ProductId = MS.ProductId
    AND MS2.CountryCode = MS.CountryCode
    AND MS2.DataTypeId = MS.DataTypeId
WHERE MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] IS NOT NULL
AND MS.OwnerTypeId = 0
AND MS.MeasureTypeId = 4
AND MS.CountryCodeId = 389
AND MS.ProductId=@ProductId
GROUP BY MS.CompanyId, SP.IndustryName, MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] / 100, SP.ProductName)

UPDATE GBOC
    SET GBOC.KeyCategories = Final.KeyC
    FROM Sports.dbo.GlobalBrandOwnerContext AS GBOC
    INNER JOIN (   SELECT DISTINCT KeyCategories.CompanyId, KeyCategories.IndustryName, REPLACE(em_prod.dbo.StringAggregate(KeyCategories.ProductName), ',', ', ') AS KeyC
           FROM KeyCategories
           WHERE KeyCategories.Rn < 6
           GROUP BY KeyCategories.CompanyId, KeyCategories.IndustryName) AS Final
    ON Final.CompanyId = GBOC.CompanyId
    AND Final.IndustryName = GBOC.IndustryName;'
;

EXECUTE sys.sp_executesql @SQL, N'@ProductId INT', @ProductId;

FETCH NEXT FROM CurrentYearCursor
INTO @ProductId, @CurrentYear;
END;

CLOSE CurrentYearCursor;
DEALLOCATE CurrentYearCursor;

标签： sql

Answer 1

错误出现REPLACE在动态 SQL 的子查询中的函数中。你没有把那里的撇号加倍。这应该可以正常工作：

DECLARE @SQL NVARCHAR(MAX);
DECLARE @CurrentYear NVARCHAR(MAX), @ProductId NVARCHAR(MAX);

DECLARE CurrentYearCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT ProductId, CurrentYear
FROM #SubcategoryProducts;

OPEN CurrentYearCursor;

FETCH NEXT FROM CurrentYearCursor
INTO @ProductId, @CurrentYear;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL =
    N'
WITH KeyCategories AS
(   SELECT DISTINCT MS.CompanyId
  , SP.IndustryName
  , SP.ProductName
  , MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] / 100 AS Size
  , ROW_NUMBER() OVER (PARTITION BY MS.CompanyId, SP.IndustryName
                       ORDER BY MS.[' + @CurrentYear + N'] * MS2.[' +     @CurrentYear + N'] / 100 DESC) AS Rn
FROM Sports.dbo.GlobalBrandOwnerContext AS GBOC
INNER JOIN dbo.MarketShare AS MS
    ON GBOC.CompanyId = MS.CompanyId
INNER JOIN #SubcategoryProducts AS SP
    ON SP.ProductId = MS.ProductId
    AND SP.DataTypeId = MS.DataTypeId
INNER JOIN dbo.MarketSize AS MS2
    ON MS2.ProjectCode = MS.ProjectCode
    AND MS2.ProductId = MS.ProductId
    AND MS2.CountryCode = MS.CountryCode
    AND MS2.DataTypeId = MS.DataTypeId
WHERE MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] IS NOT NULL
AND MS.OwnerTypeId = 0
AND MS.MeasureTypeId = 4
AND MS.CountryCodeId = 389
AND MS.ProductId=@ProductId
GROUP BY MS.CompanyId, SP.IndustryName, MS.[' + @CurrentYear + N'] * MS2.[' + @CurrentYear + N'] / 100, SP.ProductName)

UPDATE GBOC
    SET GBOC.KeyCategories = Final.KeyC
    FROM Sports.dbo.GlobalBrandOwnerContext AS GBOC
    INNER JOIN (   SELECT DISTINCT KeyCategories.CompanyId, KeyCategories.IndustryName, REPLACE(em_prod.dbo.StringAggregate(KeyCategories.ProductName), '','', '', '') AS KeyC
           FROM KeyCategories
           WHERE KeyCategories.Rn < 6
           GROUP BY KeyCategories.CompanyId, KeyCategories.IndustryName) AS Final
    ON Final.CompanyId = GBOC.CompanyId
    AND Final.IndustryName = GBOC.IndustryName;'
;

EXECUTE sys.sp_executesql @SQL, N'@ProductId INT', @ProductId;

FETCH NEXT FROM CurrentYearCursor
INTO @ProductId, @CurrentYear;
END;

CLOSE CurrentYearCursor;
DEALLOCATE CurrentYearCursor;