sql-server - 使用 SQL 子查询或动态查询更新配置表中的一个表列值

Question

我的 SQL Server 中有两个表

表号1

CREATE TABLE #Config
(
  varColumnName VARCHAR(200) ,
  varAliasName VARCHAR(200)
)

INSERT INTO #Config (varColumnName, varAliasName)
VALUES ('Consumed (Strips)', 'decQuantity'),
       ('Closing (Strips)', 'decClosing')

表号2

CREATE TABLE #Data
(
    decQuantity DECIMAL(18, 2) ,
    decClosing DECIMAL(18, 2) ,
    varInvalidRemarks VARCHAR(MAX)
)

INSERT INTO #Data (decQuantity, decClosing)
VALUES (10.10, 25.00),
       (-15.10, 45.00),
       (5.10, -10.00),
       (-25.10, -10.00)

我想从这两个表中更新#Data表的varInvalidRemarks列，并且我想要以下输出:

 decQuantity  | decClosing |  varInvalidRemarks
 -------------|------------|--------------------------
       10.10  |     25.00  |    
      -15.10  |     45.00  |  Consumed (Strips) can NOT be negetive
        5.10  |    -10.00  |  Closing (Strips) can NOT be negetive
      -25.10  |    -10.00  |  Consumed (Strips) can not be negetive,Closing(Strips) can not be negetive

我已经使用FAST FORWARD READ ONLY游标完成了这件事，但我想使用子查询或动态查询来完成此操作。

DECLARE @varColumnName VARCHAR(200) ,
@varAliasName VARCHAR(200)

DECLARE DisplayColumn CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  C.varColumnName ,
        C.varAliasName
FROM    #Config AS C

OPEN DisplayColumn

FETCH NEXT FROM DisplayColumn INTO @varColumnName, @varAliasName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('
    UPDATE  D
    SET     D.varInvalidRemarks = isnull(D.varInvalidRemarks,'''') +
    '','+@varColumnName +' can not be negative ''
    FROM    #Data AS D
    WHERE   CAST(ISNULL(D.'+@varAliasName+', 0) AS DECIMAL(18, 2)) < 0

    ')

    FETCH NEXT FROM DisplayColumn INTO @varColumnName, @varAliasName

END

CLOSE DisplayColumn
DEALLOCATE DisplayColumn

我想这样做是因为我的存储过程存在性能问题。

是否可以通过子查询来实现这个目标？

谢谢

Answer 1

这是一个动态 SQL 解决方案。请参阅动态查询的 PRINT

declare @sql    nvarchar(max),
    @col    nvarchar(max)

select  @col    = isnull(@col + 'UNION ALL' + char(13), '')
        + 'SELECT rmk = ''' + c.varColumnName + ' cannot be negative'' WHERE ' + quotename(c.varAliasName) + ' < 0' + char(13)
from    #Config c

select  @sql    = isnull(@sql, '')
        + N'UPDATE D SET varInvalidRemarks = STUFF(V.Remarks, 1, 1, '''')'  + char(13)
        + N'FROM #Data D'                           + char(13)
        + N'OUTER APPLY'                            + char(13)
        + N'('                                  + char(13)
        + N'SELECT '','' + rmk'                         + char(13)
        + N'FROM'                               + char(13)
        + N'('                                  + char(13)
        + @col                                  + char(13)
        + N') V'                                + char(13)
        + N'FOR XML PATH ('''')'                        + char(13)
        + N') V (Remarks)'                          + char(13)
FROM    #Config c

print   @col
print   @sql
exec    sp_executesql @sql